Single query to return 'count of all rows', 'count of distinct value in row' and 'count of rows having value'
Posted on 2004-11-03
If I have a table 'TABLE' like the following:
FOO BAR BAZ
----- ----- ----
A Y Z
B Y Z
B Y Q
C Y Q
C Y Q
I would like to write a single select statement that returns three values in one row:
count(distinct foo) (count of all distinct values in FOO) (Would return 3 in this example)
count() (count of all rows) (Would return 5 in this example)
count() (count of all BAZ containing 'Q') (group by baz having baz = 'Q' / Would return 3 in this example)
Make sense? Any single statement do that or must this be split into two seperate queries?
(IE: select count(distinct foo), count(1) from TABLE; select count(1) from TABLE group by BAZ having BAZ = 'Q';)