CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
user3911539
user3911539

Reputation: 1

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.

Upvotes: 0

Views: 69

Answers (3)

Mukund
Mukund

Reputation: 1689

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

Upvotes: 0

Jason W
Jason W

Reputation: 13179

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

Upvotes: 1

bummi
bummi

Reputation: 27377

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

Upvotes: 0

Related Questions