Обсуждение: 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
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.smith2 matches 95-100%2 matches 85-95%0 mathes 75-85%franz0 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
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
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
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)
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.smith2 matches 95-100%2 matches 85-95%0 mathes 75-85%franz0 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
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
Hello,
Use a view
Regards
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)
Use a view
Regards
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)
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.smith2 matches 95-100%2 matches 85-95%0 mathes 75-85%franz0 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
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