SQL creating a SUM of dollar amounts for a specific job?

Question

Here is some sample data (edited for confidentiality) from my database

Job 1 $3780.00
Job 1 $15187.50
Job 1 $8155.00
Job 2 $1485.00
Job 2 $8156.00
Job 3 $21642.00
Job 3 $15620.47
...

Here is the query currently used to pull data out. I need to add a subquery to narrow down the Jobs to unique values and sum up all of the Amounts associated with that one job into one total value

SELECT FN.full_name As [Job], transaction_date, Set1.Due_Date As [Due Date],(CASE WHEN SUM(amount_amt) > 0 THEN SUM(amount_amt) ELSE 1 END) AS [Amount], (SUM(amount_amt))-Set1.Invoice_amt AS [Balance] FROM
(((SELECT T.due_date, T.customer_id, T.transaction_id, sum(ISNULL(amount_amt_sum,0)) As Invoice_amt FROM 
(SELECT estimate.due_date, estimate.transaction_date, estimate.customer_id, estimate.transaction_id, link.transaction_2_id, (SELECT SUM(invoice.amount_amt) FROM invoice WHERE link.transaction_2_id=invoice.transaction_id AND invoice.doc_num IS NOT NULL AND invoice.doc_num<>'' AND invoice._amount_amt>0) AS amount_amt_sum FROM estimate LEFT JOIN link ON estimate.transaction_id=link.transaction_1_id GROUP BY estimate.due_date, estimate.transaction_date, estimate.customer_id, estimate.transaction_id, link.transaction_2_id) AS t GROUP BY due_date, customer_id, transaction_id)  AS Set1 LEFT JOIN estimate ON Set1.id=estimate.id) LEFT JOIN customer_fullname as FN ON Set1.customer_id=FN.id) GROUP BY Set1.Invoice_amt, Set1.Due_Date, FN.full_name, FN.name, transaction_date Having (MAX(class_id)=1 Or MAX(class_id)=2 Or MAX(class_id)=6 Or MAX(class_id)=7 Or MAX(class_id)=8 Or MAX(class_id)=9 Or MAX(class_id)=10 Or MAX(class_id)=11)

I'm not great in SQL and have no clue where to insert my subquery to get what I need. Help please?

I don't know what kind of SQL it is – Matt Leverich Mar 07 '16 at 22:34 — Matt Leverich, Mar 07 '16 at 22:34

score 0 · Accepted Answer · edited May 23 '17 at 12:23

SELECT FN.full_name As [Job], transaction_date, Set1.Due_Date As [Due Date],
(CASE WHEN SUM(amount_amt) > 0 THEN SUM(amount_amt) ELSE 1 END) AS [Amount], 
(SUM(amount_amt))-Set1.Invoice_amt AS [Balance]
 FROM
(((SELECT T.due_date, T.customer_id, T.transaction_id, 
sum(ISNULL(amount_amt_sum,0)) As Invoice_amt FROM 
(SELECT estimate.due_date, estimate.transaction_date, estimate.customer_id, 
estimate.transaction_id, link.transaction_2_id, (SELECT SUM(invoice.amount_amt) 
FROM invoice WHERE link.transaction_2_id=invoice.transaction_id AND 
invoice.doc_num IS NOT NULL AND invoice.doc_num<>'' AND invoice._amount_amt>0) 
AS amount_amt_sum FROM estimate LEFT JOIN link ON 
estimate.transaction_id=link.transaction_1_id GROUP BY estimate.due_date, 
estimate.transaction_date, estimate.customer_id, estimate.transaction_id, 
link.transaction_2_id) AS t GROUP BY due_date, customer_id, transaction_id)  AS Set1 LEFT JOIN estimate ON Set1.id=estimate.id) LEFT JOIN customer_fullname as 
FN ON Set1.customer_id=FN.id) GROUP BY Set1.Invoice_amt, Set1.Due_Date, 
FN.full_name, FN.name, transaction_date Having (MAX(class_id)=1 Or 
MAX(class_id)=2 Or MAX(class_id)=6 Or MAX(class_id)=7 Or MAX(class_id)=8 Or 
MAX(class_id)=9 Or MAX(class_id)=10 Or MAX(class_id)=11)

Whew.. now that that formatting is done.. You need a Group By clause and a sum. So... The easiest way to accomplish this, albeit probably not optimal (I expect a lot of that query could be optimized)

Select Job, Sum(Amount) TotalAmount From
(
    SELECT FN.full_name As [Job], transaction_date, Set1.Due_Date As [Due Date],
    (CASE WHEN SUM(amount_amt) > 0 THEN SUM(amount_amt) ELSE 1 END) AS [Amount], 
    (SUM(amount_amt))-Set1.Invoice_amt AS [Balance]
     FROM
    (((SELECT T.due_date, T.customer_id, T.transaction_id, 
    sum(ISNULL(amount_amt_sum,0)) As Invoice_amt FROM 
    (SELECT estimate.due_date, estimate.transaction_date, estimate.customer_id, 
    estimate.transaction_id, link.transaction_2_id, (SELECT SUM(invoice.amount_amt) 
    FROM invoice WHERE link.transaction_2_id=invoice.transaction_id AND 
    invoice.doc_num IS NOT NULL AND invoice.doc_num<>'' AND invoice._amount_amt>0) 
    AS amount_amt_sum FROM estimate LEFT JOIN link ON 
    estimate.transaction_id=link.transaction_1_id GROUP BY estimate.due_date, 
    estimate.transaction_date, estimate.customer_id, estimate.transaction_id, 
    link.transaction_2_id) AS t GROUP BY due_date, customer_id, transaction_id)  AS Set1 LEFT JOIN estimate ON Set1.id=estimate.id) LEFT JOIN customer_fullname as 
    FN ON Set1.customer_id=FN.id) GROUP BY Set1.Invoice_amt, Set1.Due_Date, 
    FN.full_name, FN.name, transaction_date Having (MAX(class_id)=1 Or 
    MAX(class_id)=2 Or MAX(class_id)=6 Or MAX(class_id)=7 Or MAX(class_id)=8 Or 
    MAX(class_id)=9 Or MAX(class_id)=10 Or MAX(class_id)=11)
) as subquery group by Job

That should do the trick..

Edit I forgot you have to name the subquery. Query edited to show this.

Edit 2 Taking a bit of a closer look at your query, you are grouping in your outer query already so we may be able to simplify things a bit, but I would definitely verify the sums to make sure everything is grouping correctly.. grouping on a bunch of different columns at once can have unexpected results if you are unfamiliar with it. I can say personally, I'm only decent at doing this.. Here is how you would extend my original post to handle multiple fields. As I mention below you need to be careful when grouping on multiple columns as you can pretty easily get unexpected results while doing this.

Select Job,transaction_date, FN.name, FN.full_name, Set1.Due_Date, Sum(Amount) TotalAmount From
(
    SELECT FN.full_name As [Job], transaction_date, Set1.Due_Date As [Due Date],
    (CASE WHEN SUM(amount_amt) > 0 THEN SUM(amount_amt) ELSE 1 END) AS [Amount], 
    (SUM(amount_amt))-Set1.Invoice_amt AS [Balance]
     FROM
    (((SELECT T.due_date, T.customer_id, T.transaction_id, 
    sum(ISNULL(amount_amt_sum,0)) As Invoice_amt FROM 
    (SELECT estimate.due_date, estimate.transaction_date, estimate.customer_id, 
    estimate.transaction_id, link.transaction_2_id, (SELECT SUM(invoice.amount_amt) 
    FROM invoice WHERE link.transaction_2_id=invoice.transaction_id AND 
    invoice.doc_num IS NOT NULL AND invoice.doc_num<>'' AND invoice._amount_amt>0) 
    AS amount_amt_sum FROM estimate LEFT JOIN link ON 
    estimate.transaction_id=link.transaction_1_id GROUP BY estimate.due_date, 
    estimate.transaction_date, estimate.customer_id, estimate.transaction_id, 
    link.transaction_2_id) AS t GROUP BY due_date, customer_id, transaction_id)  AS Set1 LEFT JOIN estimate ON Set1.id=estimate.id) LEFT JOIN customer_fullname as 
    FN ON Set1.customer_id=FN.id) GROUP BY Set1.Invoice_amt, Set1.Due_Date, 
    FN.full_name, FN.name, transaction_date Having (MAX(class_id)=1 Or 
    MAX(class_id)=2 Or MAX(class_id)=6 Or MAX(class_id)=7 Or MAX(class_id)=8 Or 
    MAX(class_id)=9 Or MAX(class_id)=10 Or MAX(class_id)=11)
) as subquery group by Job, transaction_date, FN.name, FN.full_name, Set1.Due_Date

You may be able to add the job group into the last existing group by statement, but you would have to play with it, especially because it looks like you are selecting multiple aggregate functions that look to be different for each line item (referring to Amount and Balance). Keep in mind, how grouping works is highly dependent on what you are trying to group together, so without knowing, I can simply provide the basics, or some suggestions and let you run with it.

Grouping on multiple columns

The query was heavily edited for confidentiality. I didn't realize you could put the entire query like that in a subquery. I will try it out — Matt Leverich, Mar 07 '16 at 22:50
One more thing - I still need to show the Due Date, Transaction Date, and several other fields I hid for confidentiality. It keeps telling me "Function or Column reference to "Transaction Date" must also appear in a GROUP BY. However when I add those fields to the GROUP BY I lose my distinct Job query from before. — Matt Leverich, Mar 07 '16 at 23:03
Basically you have to use either aggregate methods or a field that has been grouped when selecting. If all other fields besides job will be identical for every row that is grouped together, then you can simply add them to the grouped by clause. If not, I recommend stashing the grouped by clause as a join and selecting from it. I will try and provide something more concrete tomorrow morning. — Nate M., Mar 08 '16 at 06:19

SQL creating a SUM of dollar amounts for a specific job?

1 Answers1