hi everyone,

i have problem with writing this sql

say i have a table like this:

a b c d
-----------
1 2 1 3
4 2 1 4
1 2 4 3

i want to write a sql to count how many rows(as count1) are (a=1 & b=2) and how many rows(as count2) are (c=1), also group by d
so the result should be:

d=3: count1=2 count2=1
d=4: count1=0 count2=1

how to write this sql??

Hi,

To count how many rows are (a=1 & b=2),
SELECT count(*) FROM #table1 WHERE a=1 and b=2

To count how many rows are (c=1)
SELECT count(*) FROM #table1 WHERE c=1

To count how many rows are (a=1 & b=2 & c=1)
SELECT count(*) FROM #table1 WHERE a=1 AND b=2 AND c=1

Please tell if this is not what you wanted or if you want more details.

nalla

well, what i want to know is if there is a way to use *one* sql statement to return both counts instead of those two?



SELECT count(*) FROM #table1 WHERE a=1 and b=2
SELECT count(*) FROM #table1 WHERE c=1

Hi,

You can't get those 2 from a single SQL statement.

nalla.

single SQL statement:

SELECT a.count1, b.count2
FROM
(SELECT count(*)as count1 FROM #table1 WHERE a=1 and b=2)a,
(SELECT count(*)as count2 FROM #table1 WHERE c=1) b