SQL query with SUM, INNER JOIN and GROUP BY

[BayerMeister]

Hi, I'm stuck with a query that worked fine in mySQL. Would look somewhat like this:



3 tables "A", "B", "C" with collumns
A: "id"
B: "id", "a_id" - a foreign key to the "A" table
C: "id", "b_id" - a foreign key to the "B" table, "qty" - some data to SUM()

So the relationship is "A --1:n-- B --1:n-- C"

SELECT A.id, B.id, COUNT(C.id) AS count_c, SUM(C.qty) AS qty_c
FROM (A INNER JOIN B ON A.id = B.a_id) INNER JOIN C ON B.id = C.b_id
GROUP BY B.id

I simplified it a lot, hope that makes no difference (instead of selecting ID's I select useful information from A and B). My Access does not speak english so I can't really say the exact error message. Something like "the a.id is not part of the aggregation function".

Anyone knows what the heck it wants? The query seems pretty understandable to me

[weekend00]

add a.id in group by list. every fields(except aggregate fields) in select list must in group by list.

SELECT A.id, B.id, COUNT(C.id) AS count_c, SUM(C.qty) AS qty_c
FROM (A INNER JOIN B ON A.id = B.a_id) INNER JOIN C ON B.id = C.b_id
GROUP BY B.id, A.id

[BayerMeister]

OK, thank you very much. It works.

I changed the SELECT part to show data instead of IDs and put them into the GROUP BY. I left the IDs there too (in GROUP BY), because data needn't be unique.

It took me however longer, 'cause little typos are hard to find with the error messages Access gives.