SQL Sum Query Help

[jamesjyu]

I'm trying to formulate a query:

I have two tables, A and B. Table A has an id, A.id, which associated with a column in B (B.A_id). I also have a column in B that is kind of like a tally (B.tally).

What I need is to query for each row in A, sorted by the sum of the tallies associated with each row of A in B.

For example:

Table A ID
row 1: 0
row 2: 1
row 3: 2

Table B A_ID Tally
row 1: 0 1
row 2: 1 1
row 3: 2 -1
row 4: 1 -1
row 5: 1 -1

What I want is to sort like this:

A.ID Sum of tallies
row 1: 0 1
row 2: 1 -1 (= 1 - 1 - 1)
row 3: 2 1

What I tried to do is this:

SELECT A.ID, SUM(B.Tally) as Tally
FROM A, B
GROUP BY A.ID
WHERE A.ID = B.A_ID
ORDER BY Tally

But this doesn't work.

Any suggestions?

 

[JarlH]

You were so close... Just put the GROUP BY clause after the WHERE clause instead of before.

SELECT A.ID, SUM(B.Tally) as Tally
FROM A, B
WHERE A.ID = B.A_ID
GROUP BY A.ID
ORDER BY Tally


/Jarl

 

[PHV]

SELECT A.ID, SUM(B.Tally) AS Tally
FROM A INNER JOIN B ON A.ID = B.A_ID
GROUP BY A.ID
ORDER BY 2

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886