Thread: SQL Question - Using Group By

SQL Question - Using Group By

From
"Mike"
Date:
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


Re: SQL Question - Using Group By

From
"ksherlock@gmail.com"
Date:
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



Re: SQL Question - Using Group By

From
"Mike"
Date:
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



Re: SQL Question - Using Group By

From
Tomas Vondra
Date:
> 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

Re: SQL Question - Using Group By

From
Alban Hertroys
Date:
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 //