How to Display and Count multiple columns in SQL-2

Question

Table by the name Products where column consist of Apple(A) and Grape(G).

A G

1 1    
1 1
1 Null

I ran the Query..

Select count(Apple) as 'Apple' count(Grape) as 'Grape' 
from products 
where Apple = 1 and Grape = 1

The Output which i get from the above query is

Apple=2
Grape=2

Where i should be getting Apple=3 and Grape=2. Kindly help.

Answer 1

create table AppleGrape(A int, B int)

go
insert into AppleGrape values(1,1)
insert into AppleGrape values(1,1)
insert into AppleGrape values(1,NULL)

SELECT sum(A) as 'Apple',sum(isnull(B,0)) as 'Grape'
FROM AppleGrape

check fiddle too. http://sqlfiddle.com/#!3/126e05/5

Answer 2

Your where condition is only including rows where Apple = 1 and Grape = 1 so the complete row with grape NULL is ignored.

Answer 3

It seems this may be safer if you need a total. It handles null values more safely and doesn't depend on different behaviors from the aggregates default handling of nulls, which can trip some queries up.

SELECT SUM(ISNULL(Apple,0)) AS Apple, SUM(ISNULL(Grapes,0)) AS Grape FROM Products