GROUP BY's on 3 tables in one SELECT?

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?
On Thu, 18 Aug 2005 12:51:03 -0700, Martin S. <Martin
S.@.discussions.microsoft.com> 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)
|||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.@.discussions.microsoft.com> wrote:
>
> 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)
>
|||On Thu, 18 Aug 2005 16:12:02 -0700, Martin S. wrote:
(snip)
>Unfortunately, the tables were not created by me (I just want to extract
>something), so I can't include any CREATE TABLE statement.
Hi Martin,
The website I refered you to includes a description of how you can
easily create a script from your database. It also has a link to a
script that will generate INSERT statements from existing data.
The sample data you posted doesn't help much either, as their formatting
is off (at least in my news reader). There's a reason I asked you to
post the sample data as INSERT statements, you know...
Anyway, based on your narrative and my limited understanding of the
sample data you posted, the following might work:
SELECT T1.Person_ID, T1.Product_ID, MAX(Costs),
(SELECT SUM(T2b.Balancies)
FROM T3, T2 as T2b
WHERE T2b.PersonID = T1.PersonID
AND T2b.Product_ID = T3.Product_ID
AND T3.Product_Type = 'BB')
FROM T1, T2
WHERE (T1.Product_ID = T3.Product_ID) AND (T3.Product_Type='AA')
GROUP BY T1.Person_ID, T1.Product_ID
(untested)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks for your time (and sorry for missing DDL), the issue was finally
solved using LEFT JOIN.
Martin