Re: Sum and count weird results

Christoph Haller wrote:

> Michalis Kabrianis wrote:
> 
> 
>>Hi all,
>>I have these tables
>>
>>a (
>>id integer,
>>email varchar);
>>
>>b (
>>seat varchar,
>>transactionid varchar);
>>
>>c (
>>transactionid varchar,
>>totalprice numeric(8.2));
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 8: explain analyze is your friend
> 
> 
> And?
> 

Oops, accidental "send button" hit.
I beleived it would never reach the list.
Sorry for that.

Here we go again:
Hi all,
I have these tables

a (
id integer,
email varchar);

b (
seat varchar,
transactionid varchar references c(varchar));

c (
transactionid varchar,
a_id integer references a(id),
totalprice numeric(8.2));

sample data :
table a
1,test1@test.gr
2,test2@test.gr

table b
1,123
2,123
3,123
4,125
5,125
6,127

table c
123,1,200
125,2,100
127,1,300

What I want is to count the seatnr located on table b, and sum the 
totalprice located on table c, group by email located on table a.


I tried something like :
select sum(totalprice), count(seatnr), email from a,b,c where 
c.transactionid=b.transactionid and c.a_id=a.id

I get correct seatnr count, but wrong (and I understand why) totalprice sum.
Any good ideas on how can that be accomplished in one query?

Ideal results :
SUM    COUNT    EMAIL
4    500    test1@test.gr
2    100    test2@test.gr

Thanks in advance

Michalis