How to Display and Count multiple columns in SQL-2

问题:

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.

回答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

回答2:

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

回答3:

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