From: Janek Sendrowski <janek12@web.de> To: pgsql-general@postgresql.org Sent: Friday, August 16, 2013 11:55 AM Subject: Re: [GENERAL] devide and summarize sql result (all)
Thanks for your Answers,
my problem is, that there is no column with the name 'percentage'.
It's just a result of my query. So how can I use it anyway?
Should I just store the result in a record variable and do another query?
Janek
Gesendet: Freitag, 16. August 2013 um 00:24 Uhr Von: bricklen <bricklen@gmail.com> An: "Janek Sendrowski" <janek12@web.de> Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org> Betreff: Re: [GENERAL] devide and summarize sql result (all)
On Thu, Aug 15, 2013 at 1:51 PM, Janek Sendrowski <janek12@web.de> wrote:
Hi,
My sql query results sth. like this:
user percentage franz 78% smith 98% franz 81% jason 79%
smith 89%
smith 85%
smith 99%
Now I'd like to summarize the percentages oder every user like this.
smith
2 matches 95-100%
2 matches 85-95%
0 mathes 75-85%
franz
0 mathes 95-100%
...
A CASE statement should work, if you are willing to hard-code the list of expressions.
SELECT username, sum(case when avg between 76 and 85 then 1 else 0 end) as "76 to 85", sum(case when avg between 86 and 95 then 1 else 0 end) as "86 to 95", sum(case when avg > 95 then 1 else 0 end) as ">95" FROM yourtable GROUP BY username