Re: GROUP BY's on 3 tables in one SELECT?

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)
.

Relevant Pages

  • GROUP BYs on 3 tables in one SELECT?
    ... T1 (Person_ID, Product_ID, Costs) ... T2 (Person_ID, Product_ID, Balancies) ... I have simple GROUP BY query: ... Prev by Date: ...
    (microsoft.public.sqlserver.mseq)
  • Date query in Acceess
    ... I want to have a query that is calculating sum of the costs for each ... First, I made a query that has 3 fields: name, date and costs. ... I mean it shows the missions in the ...
    (microsoft.public.access.queries)
  • Re: "Square boxes"
    ... If you can post details about the table's structure, ... the generic query I suggested earlier ... Infopath used to fill out repeating tables, ... >>> boxes already there. ...
    (microsoft.public.access.queries)
  • Re: How to design a query for grouping totals by months?
    ... A crosstab generally generates just one sequence of data, so, I will focus ... 2 numeric fields for product costs of two ... Now I would like to design a query such that it will basically have 12 ... and the second row is for the costs and prices of part B, ...
    (microsoft.public.access.queries)
  • Re: Running an update query based on aggregate result of a select quer
    ... the sum of values in another table and I don’t understand what I’m doing wrong. ... I have a query that gives me the total of the salaries in the employees table (qryEmployeeCosts) and I want to use the result from that query to update the Salaries field in the Costs table using an update query. ...
    (microsoft.public.access.queries)