SQL query using "Group By"

BananaJim · February 23rd, 2007, 03:36 PM

Please can you tell me how to write the following SQL query.

Essentially, I have 2 tables (primary keys marked with *):

Code:

TABLE_A
================
*pk   ref   number   type
1     1     67       0
2     2     82       0
3     1     13       0
4     4     98       1
5     3     102      1 
6     2     64       0

TABLE_B
================
*ref  name
1     aaa
2     bbb
3     ccc
4     ddd

I want to do a "group by" query on Table A, so as to group the ref fields where type=0 and do a sum of their associated numbers. To get this this:

Code:

ref  SUM(number)   type
1    80            0
2    146           0

I can do this OK, but here is the problem: I need to ALSO have (in my returned recordset) the name of each ref from Table B.

So overall I want this returned:

Code:

name   ref  sum(number)   type
aaa    1    80            0
bbb    2    146           0

So far I have not found the right structure to the SQL query.

If I try this...

Code:

SELECT Table_B.name, Table_A.ref, SUM(number) AS total FROM Table_A, Table_B WHERE Table_A.type = 0 AND Table_A.ref = Table_B.ref GROUP BY Table_A.ref

...I get the error that Table_B.name cannot be returned in the query as it cannot be associated with the "group by" of Table_A.ref

I've tried other variations, including INNER JOIN and nested SELECTS, but haven't got it right.

Please can you help?

Thanks.

lethe · February 24th, 2007, 10:31 PM

how about this:

Code:

select Table_B.name, Table_B.name, TempGroupTable.sum
from
(select Table_A.ref,sum(number) as sum from Table_A group by TableA.ref)
as TempGroupTable
inner join Table_B
on TempGroupTable.ref = Table_B.ref

in other words, create the grouped result set and then join it.

This method is problematic if you want to use a "with rollup" clause, but if you don't, then it should be OK.

BananaJim · February 26th, 2007, 10:23 AM

Excellent - that works.

My SQL statement ended up a bit more complex than that, but the key thing is creating a tempTable and inner-joining that with the other table.

Thanks!