SQL Select Statement with nested group by clause

Question

I have 2 tables. I need to select the column name and a calculated field from Invoices called balance_due.

The result of the query should be the name and their balance due from all of their records combined.

Thanks for any help.

sqlfiddle could turn out useful – guido Jun 08 '13 at 02:23 — guido, Jun 08 '13 at 02:23

score 1 · Answer 1 · answered Jun 08 '13 at 03:07

1

SELECT v.vendor_name, i.totalbalance
FROM Vendors as v
     INNER JOIN (
           SELECT vendor_id, sum(invoice_total-payment_total) as totalbalance
           FROM invoices
           GROUP BY vendor_id
     ) as i on i.vendor_id = v.vendor_id

answered Jun 08 '13 at 03:07

user1845791

993
2
9
17

Please edit your answer and elaborate, explaining how this will work. – Charlie Jun 08 '13 at 03:30
Thank you so much for the response. It is what I am looking for. However I get a 00933 error thrown at line 2 (command not properly ended). "FROM vendors as v" Any suggestions? – Mike Ebbert Jun 08 '13 at 03:39

Khanh TO · Accepted Answer · 2013-06-08T08:40:41.360

0

Or there is another syntax:

;With i As
(
   SELECT vendor_id, sum(invoice_total-payment_total) as totalbalance
   FROM invoices
   WHERE payment_total is not null
   GROUP BY vendor_id
)
SELECT Vendors.vendor_name, i.totalbalance
From Vendors LEFT JOIN i ON Vendors.vendor_id = i.vendor_id

edited Jun 08 '13 at 08:40

answered Jun 08 '13 at 05:09

Khanh TO

48,509
13
99
115

This worked well. Is there any way to exclude rows from the SUM function where payment_total is null? – Mike Ebbert Jun 08 '13 at 08:39
@Mike Ebbert: add a where statement to it. See my updated answer – Khanh TO Jun 08 '13 at 08:41
What's the `;` in the beginning for? – malana Aug 29 '14 at 07:33
@lmichelbacher: You can take a look at :http://stackoverflow.com/questions/710683/when-should-i-use-semicolons-in-sql-server – Khanh TO Aug 29 '14 at 13:14

SQL Select Statement with nested group by clause

2 Answers2