Re: [GENERAL] devide and summarize sql result
Yeah, I have written that sort of query too, but with more info on tables and the SQL you are unlikely to get much help Sent from my iPad On Aug 15, 2013, at 2:46 PM, "Janek Sendrowski" wrote: > Hi, > > My sql query results sth. like this: > > user percentage > franz 78% > smith 98% > franz 81% > jason > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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
[GENERAL] devide and summarize sql result (all)
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% ... Hope there is someone who can help me Janek Sendrowksi
[GENERAL] devide and summarize sql result
Hi, My sql query results sth. like this: user percentage franz 78% smith 98% franz 81% jason -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
