Re: Doubt about SELECT

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