SUM subquery for total amount for each line

Question

I have information in a CheckLine table that essentially breaks down the various fees and information on a particular check. For this query I am wanting to SUM the total of the CheckLine but it is instead giving a SUM for ALL CheckLines together. I know I am missing something blatantly obvious, but I keep scratching my head on why I individualize the SUMs. Here is my query:

SELECT DISTINCT
O.FileNumber
,(SELECT DISTINCT
        SUM(CL.Amount)
    FROM
        dbo.Orders O
        LEFT JOIN dbo.Checks C
            ON O.OrdersID = C.OrdersID
        LEFT JOIN dbo.CheckLine CL
            ON C.ChecksID = CL.ChecksID
) AS 'Total'
FROM
dbo.Orders O
LEFT JOIN dbo.Checks C
    ON O.OrdersID = C.OrdersID
LEFT JOIN dbo.CheckLine CL
    ON C.ChecksID = CL.ChecksID

This is what it is returning:

| FileNumber  | … | Total |
|     1       |   | 2000  |
|     2       |   | 2000  |

What it should be returning is:

| FileNumber  | … | Total |
|     1       |   | 700   |
|     2       |   | 1300  |

Thoughts on my complete brain fart here? Thanks guys!

score 0 · Answer 1 · answered Jul 23 '14 at 22:03

0

You probably want this:

SELECT O.FileNumber, SUM(CL.Amount) as total
  FROM dbo.Orders O
  LEFT JOIN dbo.Checks C
    ON O.OrdersID = C.OrdersID
  LEFT JOIN dbo.CheckLine CL
    ON C.ChecksID = CL.ChecksID
 group by O.FileNumber

answered Jul 23 '14 at 22:03

Brian DeMilia

13,103
1
23
33

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

0

SELECT
  O.FileNumber,
  O.CloseDate,
  SUM(CL.Amount) as Total
FROM dbo.Orders O
    LEFT JOIN dbo.Checks C
        ON O.OrdersID = C.OrdersID
    LEFT JOIN dbo.CheckLine CL
        ON C.ChecksID = CL.ChecksID
 GROUP BY O.FileNumber, O.CloseDate

When you calculate Total in a subquery, that value will be treated as constant by SQL Server that will repeat every row.

It is very common to confuse GROUP BY with DISTINCT (please look at here and here) since they return the same values if no aggregation function is in the SELECT clause. In your example:

SELECT DISTINCT FileNumber FROM ORDERS

will return the same of

SELECT FileNumber FROM ORDERS GROUP BY FileNumber

Use GROUP BY if you are wanting to aggregate information (like your field TOTAL).

edited May 23 '17 at 11:50

Community

1
1

answered Jul 23 '14 at 22:04

Nizam

4,569
3
43
60

The issue with this that I am also selecting other fields, so when I try to `GROUP BY` I am receiving aggregate errors: "`Column 'dbo.Orders.CloseDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.`" This is why I was trying to do a subquery. – PicoDeGallo Jul 24 '14 at 15:02
The golden rule is : every column in `SELECT` which is not being aggregated should be in `GROUP BY` – Nizam Jul 24 '14 at 15:12
I have edited my answer to answer your comment. Are you wanting to have the sum for each FileNumber and CloseDate? If so, the edition in query solves your problem. If not, probably you would want to aggregate the `CloseDate`too, using `MAX` or `MIN` functions maybe. – Nizam Jul 24 '14 at 15:15
1

Thank you @Nizam. Since I was selecting multiple fields, I ended up grouping by the FileNumber and CloseDate, and placing a `MAX()` aggregate to each of my other select fields since these were all just duplicating anyway. I appreciate your help! – PicoDeGallo Jul 24 '14 at 20:17

SUM subquery for total amount for each line

2 Answers2