t-sql query not showing expected result

Question

I have sales table data as shown below

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?

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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.

Answer 5

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.