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