devide and summarize sql result (all)
<html><head></head><body><div style="font-family: Verdana;font-size: 12.0px;"><div>Hi,<br/>
<br/>
My sql query results sth. like this:<br/>
<br/>
user percentage<br/>
franz 78%<br/>
smith 98%<br/>
franz 81%<br/>
jason 79%</div>
<div>smith 89%</div>
<div>smith 85%</div>
<div>smith 99%</div>
<div> </div>
<div>Now I'd like to summarize the percentages oder every user like this.</div>
<div>smith</div>
<div>2 matches 95-100%</div>
<div>2 matches 85-95%</div>
<div>0 mathes 75-85%</div>
<div> </div>
<div>franz</div>
<div>0 mathes 95-100%</div>
<div>...</div>
<div> </div>
<div>Hope there is someone who can help me</div>
<div> </div>
<div>Janek Sendrowksi</div></div></body></html>
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
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
<html><head></head><body><div style="font-family: Verdana;font-size: 12.0px;"><div>
<div>Thanks for your Answers,</div>
<div> </div>
<div>my problem is, that there is no column with the name 'percentage'.</div>
<div>It's just a result of my query. So how can I use it anyway?</div>
<div>Should I just store the result in a record variable and do another query?</div>
<div> </div>
<div>Janek</div>
<div>
<div name="quote" style="margin:10px 5px 5px 10px; padding: 10px 0 10px 10px; border-left:2px solid #C3D9E5; word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;">
<div style="margin:0 0 10px 0;"><b>Gesendet:</b> Freitag, 16. August 2013 um 00:24 Uhr<br/>
<b>Von:</b> bricklen <bricklen@gmail.com><br/>
<b>An:</b> "Janek Sendrowski" <janek12@web.de><br/>
<b>Cc:</b> "pgsql-general@postgresql.org" <pgsql-general@postgresql.org><br/>
<b>Betreff:</b> Re: [GENERAL] devide and summarize sql result (all)</div>
<div name="quoted-content">
<div>
<div class="gmail_extra">
<div class="gmail_quote">On Thu, Aug 15, 2013 at 1:51 PM, Janek Sendrowski <span><<a href="janek12@web.de" target="_parent">janek12@web.de</a>></span> wrote:
<blockquote class="gmail_quote" style="margin: 0.0px 0.0px 0.0px 0.8ex;border-left: 1.0px solid rgb(204,204,204);padding-left: 1.0ex;">
<div>
<div style="font-family: Verdana;font-size: 12.0px;">
<div>Hi,<br/>
<br/>
My sql query results sth. like this:<br/>
<br/>
user percentage<br/>
franz 78%<br/>
smith 98%<br/>
franz 81%<br/>
jason 79%</div>
<div>smith 89%</div>
<div>smith 85%</div>
<div>smith 99%</div>
<div> </div>
<div>Now I'd like to summarize the percentages oder every user like this.</div>
<div>smith</div>
<div>2 matches 95-100%</div>
<div>2 matches 85-95%</div>
<div>0 mathes 75-85%</div>
<div> </div>
<div>franz</div>
<div>0 mathes 95-100%</div>
<div>...</div>
</div>
</div>
</blockquote>
</div>
</div>
<div class="gmail_extra">A CASE statement should work, if you are willing to hard-code the list of expressions.<br/>
<br/>
SELECT username,<br/>
sum(case when avg between 76 and 85 then 1 else 0 end) as "76 to 85",<br/>
sum(case when avg between 86 and 95 then 1 else 0 end) as "86 to 95",<br/>
sum(case when avg > 95 then 1 else 0 end) as ">95"<br/>
FROM yourtable<br/>
GROUP BY username</div>
<div class="gmail_extra"> </div>
</div>
</div>
</div>
</div>
</div></div></body></html>
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)
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