Thread: SQL Question - Using Group By
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 But that doesn't give me how many active and inactive each had! Please help me here understand how to approach this. Thank you, Mike
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"
On Feb 23, 2:50 pm, "Mike" <aki...@gmail.com> wrote:
> 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
>
> But that doesn't give me how many active and inactive each had!
>
> Please help me here understand how to approach this.
>
> Thank you,
> Mike
Thank you! Exactly what I needed.
Mike
On Feb 23, 4:42 pm, "ksherl...@gmail.com" <ksherl...@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"
>
> On Feb 23, 2:50 pm, "Mike" <aki...@gmail.com> wrote:
>
> > 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
>
> > But that doesn't give me how many active and inactive each had!
>
> > Please help me here understand how to approach this.
>
> > Thank you,
> > 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 //