t-sql query not showing expected result

Question

I have sales table data as shown below

Sales Table Data http://lh5.ggpht.com/_KT7tmVVBHFM/TCryAax1JlI/AAAAAAAAAFk/zahMq4RoOuw/s144/Sales.png

I want it to display group wise sales according to Date. Sales table contains data for different groups but my query shows only two rows.

The SQL Query:

select 
    i.gName, 
    sum(Quantity) as '180ml', 
    isnull((select sum(Quantity)
        from saleslog 
        where BillDate='12-10-2010' 
        and pSize=375 and pGroup=i.gCode),0) as '375ml', 
    isnull((select sum(Quantity)
        from saleslog 
        where BillDate='12-10-2010' 
        and pSize=500 and pGroup=i.gCode),0) as '500ml', 
    isnull((select sum(Quantity)
        from saleslog 
        where BillDate='12-10-2010' 
        and pSize=750 and pGroup=i.gCode),0) as '750ml', 
    isnull((select sum(Quantity)
        from saleslog 
        where BillDate='12-10-2010' 
        and pSize=1000 and pGroup=i.gCode),0) as '1000ml', 
    isnull((select sum(Quantity)
        from saleslog 
        where BillDate='12-10-2010' 
        and pSize=2000 and pGroup=i.gCode),0) as '2000ml' 
from saleslog as s
    inner join ItemGroup as i on s.pGroup=i.gCode 
where BillDate='12-10-2010' 
    and i.gCode=pGroup 
    and pSize=180 
group by i.gCode,i.gName

Output of above query

WHISKY 5 2 0 0 0 0
RUM     82 0 0 45 0 0

It is showing these results, but I expected it to list all product groups as follows:

Product Group Table :

1 BRANDY         1
2 WHISKY         2
3 RUM         3
4 GIN         4
5 VODKA         5
6 BEER         8
7 WINE         6
8 LIQUOR         7
9 SCOTCH WHY 9
10 LUBRICANT 15
11 UNTAXABLE 16
12 O/S LIQUOR 10
13 RTD         11
14 275 ML         12

What's wrong with my query?

Love the thumbnail of the data. Too bad it is too small to read. You could also think about formatting that massive SQL statement so it is not on one line and can be read without scrolling. — Oded
– Oded, Commented Jun 30, 2010 at 7:44
Could you break your query into a few more lines, please? One long line is hard to read and understand. — Brian Hooper
– Brian Hooper, Commented Jun 30, 2010 at 7:45
Agreed, you need to sort it out so that we can see what data is there — kevchadders
– kevchadders, Commented Jun 30, 2010 at 7:49
did you even try that? I clicked it, and guess what. I get a tiny, unreadable thumbnail all by itself in my browser. — Oded
– Oded, Commented Jun 30, 2010 at 7:58

Blorgbeard · Accepted Answer · 2010-06-30 07:52:21Z

1

Ok. I think you need to move the 180mL query into a subquery with the others. Something like this:

select i.gName,
isnull((select sum(Quantity)from saleslog where BillDate='12-10-2010' and pSize=180 and pGroup=i.gCode),0) as '180ml', 
isnull((select sum(Quantity)from saleslog where BillDate='12-10-2010' and pSize=375 and pGroup=i.gCode),0) as '375ml', 
isnull((select sum(Quantity)from saleslog where BillDate='12-10-2010' and pSize=500 and pGroup=i.gCode),0) as '500ml',
isnull((select sum(Quantity)from saleslog where BillDate='12-10-2010' and pSize=750 and pGroup=i.gCode),0) as '750ml',
isnull((select sum(Quantity)from saleslog where BillDate='12-10-2010' and pSize=1000 and pGroup=i.gCode),0) as '1000ml',
isnull((select sum(Quantity)from saleslog where BillDate='12-10-2010' and pSize=2000 and pGroup=i.gCode),0) as '2000ml' 
from saleslog as s 
inner join ItemGroup as i on s.pGroup=i.gCode 
where BillDate='12-10-2010' 
group by i.gCode, i.gName

answered Jun 30, 2010 at 7:52

Blorgbeard

104k50 gold badges237 silver badges276 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Raj Over a year ago

OK its showing all group, thanks.. programming, 1 code error or misplace can do havoc. thanks again for prompt reply....

AdaTheDev Over a year ago

But if there's not SalesLog record for an item on 12-10-2010, then no record will be returned for that group still.

marc_s · Accepted Answer · 2010-06-30 07:52:49Z

1

What datatype is this BillDate??

If it is DATETIME, then this statement here

BillDate = '12-10-2010'

will only select those purchases made on 12-10-2010 at midnight (0:00:00 hours).

You need to be more careful with your date queries! DATETIME always also contains a time portion - so if you want all purchases on the 12-10-2010, you need to use:

WHERE BillDate BETWEEN '12-10-2010 00:00:00' AND  '12-10-2010 23:59:59'

or alternatively:

WHERE DAY(BillDate) = 12 AND MONTH(BillDate) = 10 AND YEAR(BillDate) = 2010

answered Jun 30, 2010 at 7:52

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

1 Comment

AdaTheDev Over a year ago

Good point - though I'd use: BillDate >= '2010-10-12' AND BillDate < '2010-10-13' as I've seen records missed before from the last second of the day with the BETWEEN approach (i.e. any records 23:59:59.001 to 23:59:59.999(

AdaTheDev · Accepted Answer · 2010-06-30 07:47:04Z

0

Try changing your FROM clause onwards to:

from ItemGroup as i
    LEFT OUTER JOIN saleslog as s ON i.gCode = s.pGroup AND s.BillDate = '12-10-2010' AND s.pSize=180
group by i.gCode,i.gName

answered Jun 30, 2010 at 7:47

AdaTheDev

148k28 gold badges215 silver badges204 bronze badges

Comments

Matt Mitchell · Accepted Answer · 2010-06-30 07:54:03Z

0

That query makes no sense semantically but works once you think hard about it.

For instance:

You are selecting the SUM of all sales for the group as your 180ml:

sum(Quantity) as '180ml'

But you are conditioning by the size of 180ml on the total to counter that

where pSize=180

I would say it is because the other groups don't have 180ml sizes for sale.

answered Jun 30, 2010 at 7:54

Matt Mitchell

42k35 gold badges122 silver badges186 bronze badges

Comments

devio · Accepted Answer · 2010-06-30 08:01:19Z

0

You query only returns product groups which have at least 1 180ml sale. Change the INNER JOIN to a subselect, as you did with the other product sizes, and you will get all product groups in the result set.

answered Jun 30, 2010 at 8:01

devio

37.3k7 gold badges85 silver badges147 bronze badges

Collectives™ on Stack Overflow

t-sql query not showing expected result

5 Answers 5

2 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related