Обсуждение: 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 //