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:10:26 -0400
Message-ID: <5.1.0.14.2.20020419160940.03e8ae50@mail.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%





Brian Kosick
Web Programmer
New Age Consulting Service, Inc.
216-619-2000
briank@nacs.net




view thread (3+ messages)  latest in thread

Message-ID: <5.1.0.14.2.20020419160940.03e8ae50@mail.nacs.net>
Permalink:  ../5.1.0.14.2.20020419160940.03e8ae50@mail.nacs.net/
Also on:    postgresql.org/message-id/5.1.0.14.2.20020419160940.03e8ae50@mail.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: <5.1.0.14.2.20020419160940.03e8ae50@mail.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