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;