Message-ID: <411A51B9.6000400@interzone.gr>
Date: Wed, 11 Aug 2004 10:04:57 -0700
From: Michalis Kabrianis <mk@interzone.gr>
Organization: Interzone
User-Agent: Mozilla Thunderbird 0.7.3 (Windows/20040803)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: Sum and count weird results
References: <410E25E7.9000809@interzone.gr> <41133298.40F075D9@rodos.fzk.de>
In-Reply-To: <41133298.40F075D9@rodos.fzk.de>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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