SQL Syntax Issue with getting sum
Ok I have two tables.
Table IDAssoc has the columnsbill_id
, year
, area_id
.
Table Bill has the columns bill_id
, year
, main_id
, and amount_due
.
I'm trying to get the sum of the amount_due
column from the bill table for each of the associated area_ids in the IDAssoc table.
I'm doing a select statement to select the sum and joining on the bill_ids. How can I set this up so it will have a single row for each of the associated bills in each area_id from the assoc table. There may be three or four bill_ids associated with each area_id and I need those summed for each and returned so I can use this select in another statement. I have a group by set up for the area_id but it still is returning each row and not summing them up for each area_id. I have the year and main_id specified already in the where clause to return the data that I want, but I can't get the sum to work properly. Sorry I'm still learning and I'm not sure how to do this. Thanks!
Edit- Basically the query I'm trying so far is basically just like the one posted below:
select a.area_id, sum(b.amount_due)
from IDAssoc a
inner join Bill b
on a.bill_id = b.bill_id
where Bill.year = 2006 and bill.bill_id = 11111
These are just arbitrary numbers. The data this is returning is like this: amount_due - area_id .05 1003 .15 1003 .11 开发者_如何学运维 1003 65 1004 55 1004
I need one row returned for each area_id with the amount_due summed. The area_id is only in the assoc table and not in the bill table.
select a.area_id, sum(b.amount_due)
from IDAssoc a
inner join Bill b
on a.bill_id = b.bill_id
where b.year = 2006 and b.bill_id = 11111
group by a.area_id
You might want to change inner join
to left join
if one IDAssoc
can have many or no Bill
:
select a.area_id, coalesce(sum(b.amount_due),0)
from IDAssoc a
left join Bill b
on a.bill_id = b.bill_id
where b.year = 2006 and b.bill_id = 11111
group by a.area_id
You are missing the GROUP BY
clause:
SELECT a.area_id, SUM(b.amount_due) TotalAmount
FROM IDAssoc a
LEFT JOIN Bill b
ON a.bill_id = b.bill_id
GROUP BY a.area_id
精彩评论