Help with a query that fetches a SUM

I am trying to get a query that will output the invoice ID, invoice title, and sum of the invoice allocation amounts for each invoice where the allocation is paid

table: invoices
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| title | varchar(255) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

+----+------------+
| id | title      |
+----+------------+
|  1 | John Smith |
|  2 | Mary Jones |
+----+------------+

Open in new window


table: invoice_amounts
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| invoice_id | int(11)       | NO   |     | NULL    |       |
| amount     | decimal(10,0) | NO   |     | NULL    |       |
| paid       | varchar(255)  | YES  |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+

+------------+--------+------+
| invoice_id | amount | paid |
+------------+--------+------+
|          1 | 26     | paid |
|          2 | 59     | NULL |
|          2 | 69     | paid |
|          2 | 30     | paid |
|          1 | 26     | paid |
+------------+--------+------+

Open in new window


Can anybody help me out? This is sort-of beyond my SQL query skills.

Thank you.
John S.Web DeveloperAsked:
Who is Participating?
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
This should do..
select i.id, i.title, sum(amount) sum_amount
from invoices i
join invoices_amount ia on i.id = ia.invoice_id
where ia.paid = 'paid'
group by i.id, i.title

Open in new window

1
 
MohitPanditCommented:
Here you go,
SELECT
	invoice_id = I.id,
	title = i.title,
	ia.invoice_sum_amount
FROM invoices i
INNER JOIN
(
	SELECT
		invoice_id,
		invoice_sum_amount = SUM(amount)
	FROM invoice_amounts
	WHERE
	(
		paid = 'paid'
	)
	GROUP BY invoice_id
) ia
ON i.id = ia.invoice_id

Open in new window

1
 
John S.Web DeveloperAuthor Commented:
Thank you. These answers helped me a lot.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
You are welcome!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.