pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Brian Kosick <briank@nacs.net>
To: pgsql-sql@postgresql.org
Subject: Trying to get query to work
Date: Fri, 19 Apr 2002 16:07:33 -0400
Message-ID: <3CC07905.70701@nacs.net> (raw)

    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%







view thread (3+ messages)  latest in thread

Message-ID: <3CC07905.70701@nacs.net>
Permalink:  ../3CC07905.70701@nacs.net/
Also on:    postgresql.org/message-id/3CC07905.70701@nacs.net

 · 

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: briank@nacs.net
  Subject: Re: Trying to get query to work
  In-Reply-To: <3CC07905.70701@nacs.net>

* 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