pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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;




view thread (3+ messages)

Message-ID: <20020420230333.E55733-100000@megazone23.bigpanda.com>
Permalink:  ../20020420230333.E55733-100000@megazone23.bigpanda.com/
Also on:    postgresql.org/message-id/20020420230333.E55733-100000@megazone23.bigpanda.com

 · 

reply

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