From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Brian Kosick <briank@nacs.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Trying to get query to work
Date: Sat, 20 Apr 2002 23:04:44 -0700 (PDT)
Message-ID: <20020420230333.E55733-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <3CC07905.70701@nacs.net>
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;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone23.bigpanda.com, briank@nacs.net
Subject: Re: Trying to get query to work
In-Reply-To: <20020420230333.E55733-100000@megazone23.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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