Re: [GENERAL] devide and summarize sql result (all)
Hello, Use a view Regards From: Janek Sendrowski 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 An: "Janek Sendrowski" Cc: "pgsql-general@postgresql.org" Betreff: Re: [GENERAL] devide and summarize sql result (all) On Thu, Aug 15, 2013 at 1:51 PM, Janek Sendrowski 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
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 An: "Janek Sendrowski" Cc: "pgsql-general@postgresql.org" Betreff: Re: [GENERAL] devide and summarize sql result (all) On Thu, Aug 15, 2013 at 1:51 PM, Janek Sendrowski <jane...@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
Re: [GENERAL] devide and summarize sql result (all)
Hi Janek, You can try: =# SELECT name, perc/5*5 || '-' || perc/5*5+5 AS range, count(*) as matches FROM test GROUP BY name, perc/5 ORDER BY perc/5; name | range | matches ---++- franz | 75-80 | 1 jason | 75-80 | 1 franz | 80-85 | 1 smith | 85-90 | 2 smith | 95-100 | 2 (5 rows) -- Beena Emerson
Re: [GENERAL] devide and summarize sql result (all)
On Thu, Aug 15, 2013 at 1:51 PM, Janek Sendrowski 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
