Re: SQL Question - Using Group By

[Tomas Vondra <tv(at)fuzzy(dot)cz>]

> Hi,
>
> I have a question about using Group By.
>
> On a table like this:
>
> Type (varchar)     |     Active (boolean)
> --------------------------------------------------------
> Type One           |      False
> Type Two           |      True
> Type One           |      True
> Type Fifty           |      Flase
> Type Two           |      True
>
> Having this table I want a report grouping Types and giving me more
> statistics such as:
>
> Type    |    Active Count    |     Inactive Count    |    Active
> Percent
>
> How do i do that?
>
> I can think of :
>
> select Type from table_name group by Type
>   
This should been quite easy - the trick is aggregate functions omit NULL 
values (maybe there is some other / better way):

SELECT type,
              COUNT(CASE WHEN active THEN 1 ELSE NULL END) AS active_count,
              COUNT(CASE WHEN active THEN NULL ELSE 1 END) AS 
inactive_count,
              COUNT(CASE WHEN active THEN 1 ELSE NULL END) / COUNT(*) 
AS active_pct
FROM table_name;

but have not tested it ;(

Tomas