From: Michalis Kabrianis <mk@interzone.gr>
To: pgsql-sql@postgresql.org
Subject: Re: Sum and count weird results
Date: Wed, 11 Aug 2004 10:04:57 -0700
Message-ID: <411A51B9.6000400@interzone.gr> (raw)
In-Reply-To: <41133298.40F075D9@rodos.fzk.de>
References: <410E25E7.9000809@interzone.gr>
<41133298.40F075D9@rodos.fzk.de>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mk@interzone.gr
Subject: Re: Sum and count weird results
In-Reply-To: <411A51B9.6000400@interzone.gr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox