Lists: | pgsql-general |
---|
From: | "Mike" <akiany(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | SQL Question - Using Group By |
Date: | 2007-02-23 19:50:51 |
Message-ID: | 1172260251.135113.112560@j27g2000cwj.googlegroups.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | "ksherlock(at)gmail(dot)com" <ksherlock(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: SQL Question - Using Group By |
Date: | 2007-02-23 21:42:41 |
Message-ID: | 1172266961.049030.91440@a75g2000cwd.googlegroups.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(dot)(dot)(dot)(at)gmail(dot)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
From: | "Mike" <akiany(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: SQL Question - Using Group By |
Date: | 2007-02-23 23:12:58 |
Message-ID: | 1172272378.356319.11660@z35g2000cwz.googlegroups.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Thank you! Exactly what I needed.
Mike
On Feb 23, 4:42 pm, "ksherl(dot)(dot)(dot)(at)gmail(dot)com" <ksherl(dot)(dot)(dot)(at)gmail(dot)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(dot)(dot)(dot)(at)gmail(dot)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
From: | Tomas Vondra <tv(at)fuzzy(dot)cz> |
---|---|
To: | Mike <akiany(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: SQL Question - Using Group By |
Date: | 2007-02-25 17:35:39 |
Message-ID: | 45E1C8EB.9030001@fuzzy.cz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
> 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
From: | Alban Hertroys <alban(at)magproductions(dot)nl> |
---|---|
To: | "ksherlock(at)gmail(dot)com" <ksherlock(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: SQL Question - Using Group By |
Date: | 2007-02-26 09:52:18 |
Message-ID: | 45E2ADD2.4070100@magproductions.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
ksherlock(at)gmail(dot)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(at)magproductions(dot)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 //