Re: Trying to get query to work

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;