Re: GROUP BY's on 3 tables in one SELECT?
- From: Martin S. <MartinS@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 18 Aug 2005 16:12:02 -0700
Hugo,
> Probably - but your question has to be clarified first. I think that
> there might be some typos in your query as well (e.g. the T2 after FROM
> should be T3, right?
Yes, You are right.
> And should the product type in the WHERE clause not
> read BB instead of AA?)
That's as I'have written. I need to find MAX(T1.Costs) for product AA and
somehow connect to it SUM(T2.Balancies) for product BB for the same person.
I see that trying to simplify my question, I've missed one field -
T1.Account_ID and T2.Account_ID. So let me write it down once again:
having 3 tables:
T1 (Person_ID, Account_ID, Product_ID, Costs)
T2 (Person_ID, Account_ID, Product_ID, Balancies)
T3 (Product_ID, Product_Type)
primary key for T1 is Person_ID together with Account_ID
primary key for T2 is Person_ID together with Account_ID
primary key for T3 is Product_ID
Account_ID's in T1 are different from Account_ID's in T2
so having GROUP BY query:
SELECT T1.Person_ID,
T1.Account_ID,
T1.Product_ID,
MAX(Costs)
FROM T1, T3
WHERE (T1.Product_ID = T3.Product_ID)
AND (T3.Product_Type='AA')
GROUP BY T1.Person_ID, T1.Account_ID, T1.Product_ID
at the end of each row obtained from previous statement, I'd like to add
sum of T2.Balancies for which (T1.Person_ID = T2.Person_ID) AND
(T2.Product_ID = T3.Product_ID) AND (T3.Product_Type='BB')
and write the whole thing using one SELECT
Unfortunately, the tables were not created by me (I just want to extract
something), so I can't include any CREATE TABLE statement.
Example:
T1
Person_ID Account_ID Product_ID Costs
----------- ------------- ------------ -------
2 254 1000 4
80 243 1000 3
80 232 1000 6
100 235 3000 8
T2
Person_ID Account_ID Product_ID Balancies
----------- ------------ ----------- ----------
1 345 4000 10
2 346 2000 10
80 376 2000 10
80 389 2000 10
80 325 5000 10
T3
Product_ID Product_Type
----------- --------------
1000 AA
2000 BB
3000 C
4000 D
5000 E
The result on previous tables should be like this:
Person_ID Account_ID Product_ID MAX(Costs) SUM(Balancies)
---------- ------------- ------------ ----------- ----------------
2 254 1000 4
10
80 243 1000 6
20
80 232 1000 6
20
Martin
"Hugo Kornelis" wrote:
> On Thu, 18 Aug 2005 12:51:03 -0700, Martin S. <Martin
> S.@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
>
> >Hi there, I'd like to ask you for help with following:
> >
> >having 3 tables:
> >T1 (Person_ID, Product_ID, Costs)
> >T2 (Person_ID, Product_ID, Balancies)
> >T3 (Product_ID, Product_Type)
> >
> >I have simple GROUP BY query:
> >SELECT T1.Person_ID, T1.Product_ID, MAX(Costs)
> >FROM T1, T2
> >WHERE (T1.Product_ID = T3.Product_ID) AND (T3.Product_Type='AA')
> >GROUP BY T1.Person_ID, T1.Product_ID
> >
> >at the end of each row obtained from previous statement, I'd like to add
> >sum of T2.Balancies for which (T1.Person_ID = T2.Person_ID) AND
> >(T2.Product_ID = T3.Product_ID) AND (T3.Product_Type='BB')
> >
> >Is it possible to do it within one SELECT statement?
>
> Hi Martin,
>
> Probably - but your question has to be clarified first. I think that
> there might be some typos in your query as well (e.g. the T2 after FROM
> should be T3, right? And should the product type in the WHERE clause not
> read BB instead of AA?)
>
> To make sure that I'll understand the question you are asking and not
> something else, I ask you to post details about your tables AS CREATE
> TABLE STATEMENTS (and please include all constraints and roperties).
> Also, add some rows of sample data (as INSERT statements) and the
> required output.
>
> See www.aspfaq.com/5006 for more details.
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
.
- Follow-Ups:
- Re: GROUP BY's on 3 tables in one SELECT?
- From: Hugo Kornelis
- Re: GROUP BY's on 3 tables in one SELECT?
- References:
- GROUP BY's on 3 tables in one SELECT?
- From: Martin S.
- Re: GROUP BY's on 3 tables in one SELECT?
- From: Hugo Kornelis
- GROUP BY's on 3 tables in one SELECT?
- Prev by Date: Re: GROUP BY's on 3 tables in one SELECT?
- Next by Date: how to write query
- Previous by thread: Re: GROUP BY's on 3 tables in one SELECT?
- Next by thread: Re: GROUP BY's on 3 tables in one SELECT?
- Index(es):
Relevant Pages
|
|
