pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTrying to get query to work
3+ messages / 2 participants
[nested] [flat]
* Trying to get query to work
@ 2002-04-19 20:07 Brian Kosick <briank@nacs.net>
0 siblings, 1 reply; 3+ messages in thread
From: Brian Kosick @ 2002-04-19 20:07 UTC (permalink / raw)
To: pgsql-sql
Hello All,
I apologize if I'm posting to the wrong place, if I should be
posting somewhere else, I would appreciate it if someone would let me
know, and accept my advance apologies. I have a table that looks like this:
id | model | make | color | count
-------------------------------------------------
1 | Toyota | Corolla | Blue | 3
2 | Toyota | Corolla | Red | 2
3 | Dodge | Avenger | Black | 1
4 | Dodge | Avenger | Red | 5
6 | Chrysler | Sebring | Beige | 8
I need to create a SQL statement that will give me results that look
like this:
model | make | color | count | total |
percentage
------------------------------------------------------------
Toyota | Corolla | Blue | 3 | 5 | 60%
Toyota | Corolla | Red | 2 | 5 | 40%
Dodge | Avenger | Black | 1 | 6 | 17%
Dodge | Avenger | Red | 5 | 6 | 83%
Chrysler | Sebring | Beige | 8 | 8 | 100%
This is the closest that I've come so far:
SELECT model, make, color, SUM(count) AS total, ((count/SUM(count))*100)
AS percentage FROM autos GROUP BY model, make, color, count;
However this produces a table that looks like this:
model | make | color | count | total |
percentage
------------------------------------------------------------
Toyota | Corolla | Blue | 3 | 3 | 100%
Toyota | Corolla | Red | 2 | 2 | 100%
Dodge | Avenger | Black | 1 | 1 | 100%
Dodge | Avenger | Red | 5 | 5 | 100%
Chrysler | Sebring | Beige | 8 | 8 | 100%
^ permalink raw reply [nested|flat] 3+ messages in thread
* Trying to get query to work.
@ 2002-04-19 20:10 Brian Kosick <briank@nacs.net>
0 siblings, 0 replies; 3+ messages in thread
From: Brian Kosick @ 2002-04-19 20:10 UTC (permalink / raw)
To: pgsql-sql
Hello All,
I apologize if I'm posting to the wrong place, if I should be
posting somewhere else, I would appreciate it if someone would let me know,
and accept my advance apologies. I have a table that looks like this:
id | model | make | color | count
-------------------------------------------------
1 | Toyota | Corolla | Blue | 3
2 | Toyota | Corolla | Red | 2
3 | Dodge | Avenger | Black | 1
4 | Dodge | Avenger | Red | 5
6 | Chrysler | Sebring | Beige | 8
I need to create a SQL statement that will give me results that look like this:
model | make | color | count | total | percentage
------------------------------------------------------------
Toyota | Corolla | Blue | 3 | 5 | 60%
Toyota | Corolla | Red | 2 | 5 | 40%
Dodge | Avenger | Black | 1 | 6 | 17%
Dodge | Avenger | Red | 5 | 6 | 83%
Chrysler | Sebring | Beige | 8 | 8 | 100%
This is the closest that I've come so far:
SELECT model, make, color, SUM(count) AS total, ((count/SUM(count))*100) AS
percentage FROM autos GROUP BY model, make, color, count;
However this produces a table that looks like this:
model | make | color | count | total | percentage
------------------------------------------------------------
Toyota | Corolla | Blue | 3 | 3 | 100%
Toyota | Corolla | Red | 2 | 2 | 100%
Dodge | Avenger | Black | 1 | 1 | 100%
Dodge | Avenger | Red | 5 | 5 | 100%
Chrysler | Sebring | Beige | 8 | 8 | 100%
Brian Kosick
Web Programmer
New Age Consulting Service, Inc.
216-619-2000
briank@nacs.net
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Trying to get query to work
@ 2002-04-21 06:04 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Brian Kosick <briank@nacs.net>
0 siblings, 0 replies; 3+ messages in thread
From: Stephan Szabo @ 2002-04-21 06:04 UTC (permalink / raw)
To: Brian Kosick <briank@nacs.net>; +Cc: pgsql-sql
On Fri, 19 Apr 2002, Brian Kosick wrote:
> Hello All,
> I apologize if I'm posting to the wrong place, if I should be
> posting somewhere else, I would appreciate it if someone would let me
> know, and accept my advance apologies. I have a table that looks like this:
pgsql-sql seems like the right place...
>
> id | model | make | color | count
> -------------------------------------------------
> 1 | Toyota | Corolla | Blue | 3
> 2 | Toyota | Corolla | Red | 2
> 3 | Dodge | Avenger | Black | 1
> 4 | Dodge | Avenger | Red | 5
> 6 | Chrysler | Sebring | Beige | 8
>
> I need to create a SQL statement that will give me results that look
> like this:
>
> model | make | color | count | total |
> percentage
> ------------------------------------------------------------
> Toyota | Corolla | Blue | 3 | 5 | 60%
> Toyota | Corolla | Red | 2 | 5 | 40%
> Dodge | Avenger | Black | 1 | 6 | 17%
> Dodge | Avenger | Red | 5 | 6 | 83%
> Chrysler | Sebring | Beige | 8 | 8 | 100%
>
> This is the closest that I've come so far:
> SELECT model, make, color, SUM(count) AS total, ((count/SUM(count))*100)
> AS percentage FROM autos GROUP BY model, make, color, count;
I think something like this might work:
select autos.model, autos.make, color, count, total, (count*100/total)
from autos, (select model,make, sum(count) as total from autos group by
model, make) as m where autos.model=m.model and autos.make=m.make group by
autos.model,autos.make,color,count,total;
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-04-21 06:04 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-04-19 20:07 Trying to get query to work Brian Kosick <briank@nacs.net>
2002-04-21 06:04 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-04-19 20:10 Trying to get query to work. Brian Kosick <briank@nacs.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox