SQL Question - Using Group By

You could use COUNT() in conjunction with NULLIF:

select "Type",
count(nullif("Active", false)) as "Active Count",
count(nullif("Active", true)) as "Inactive Count",
100 * count(nullif("Active", false)) / count(*) as "Active Percent"
from table_name group by "Type"

Thank you! Exactly what I needed.
Mike

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

ksherlock@gmail.com wrote:

You could use COUNT() in conjunction with NULLIF:

select "Type",
count(nullif("Active", false)) as "Active Count",
count(nullif("Active", true)) as "Inactive Count",
100 * count(nullif("Active", false)) / count(*) as "Active Percent"
from table_name group by "Type"

Tom Lane suggested me to use sum("Active"::int) in a similar situation;
Except that I had boolean expressions instead of values. It is a bit
faster; IMO readability is just "different".

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //