Skip site navigation (1) Skip section navigation (2)

Re: Doubt about SELECT

From: Jeff Ross <jross(at)wykids(dot)org>
To: SydMosh <elmosh19(at)hotmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Doubt about SELECT
Date: 2009-03-29 20:54:32
Message-ID: 49CFE008.5050605@wykids.org (view raw or flat)
Thread:
Lists: pgsql-general
SydMosh wrote:
> Hi, i'm kinda new on postgresql, so i have a doubt, i'm trying to make a
> query look like this:
> http://img90.imageshack.us/img90/9440/consultaen.jpg
>
> I feel like it is something so simple, show the "SUM(price_serv) AS total"
> on a new line, but i just can't find the way. I've read the postgresql
> manual over and over and i couldn't find anything there.
>
> I'm sure you guys can help me.
>   
You want two queries, joined with a union all.

create temp table test(person_id integer, service_id integer, name text, 
address text, price_serv integer);

insert into test values(1,40,'Bob Cobb','85 Cob Court, Cheyenne, WY 
82001',380);
insert into test values(1,40,'Bob Cobb','85 Cob Court, Cheyenne, WY 
82001',220);

    select
        person_id,
        service_id,
        name,
        address,
        price_serv,
        null as "total"
        from test
    union all
    select
        null as person_id,
        null as service_id,
        null as name,  
        null as address,
        null as price_serv,
        sum(price_serv) as "total"
        from test;

 person_id | service_id |   name   |             address              | 
price_serv | total
-----------+------------+----------+----------------------------------+------------+-------
         1 |         40 | Bob Cobb | 85 Cob Court, Cheyenne, WY 82001 
|        380 |     
         1 |         37 | Bob Cobb | 85 Cob Court, Cheyenne, WY 82001 
|        220 |     
           |            |          |                                  
|            |   600
(3 rows)


If you want to normalize your data, name and address should be in 
another table so the query ends up more like

    select
        test.person_id,
        test.service_id,
        people.name,
        people.address,
        test.price_serv,
        null as "total"
        from test, people where test.person_id = people.person_id
    union all
    select
        null as person_id,
        null as service_id,
        null as name,  
        null as address,
        null as price_serv,
        sum(price_serv) as "total"
        from test;

Hope that helps,

Jeff Ross


In response to

Responses

pgsql-general by date

Next:From: V S PDate: 2009-03-30 00:02:52
Subject: [Q] LOG: failed to commit client_encoding
Previous:From: Paul WehrDate: 2009-03-29 18:00:53
Subject: Using foreign key constraint to eliminate unnecessary joins in view

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group