CODEWITHSUNDEEP
sqlsql-server-2012
tdm
tdm

Reputation: 87

sum() returning multiple rows

I'm trying to get the sum of all negative values, the sum of all positive values, and a total sum. Here is my query: 

SELECT
        gl.rowno,
        gl.br
        gl.fs
        gl.cudic
        gl.name
        gl.no_
        gl.balance
        SUM(glhi.amount) AS Total,
        CASE WHEN glhi.amount >0 THEN ISNULL(SUM(glhi.amount),0) END AS TotalPositive,
        CASE WHEN glhi.amount <0 THEN ISNULL(SUM(glhi.amount),0) END AS TotalNegative
FROM gl
        INNER JOIN glhi ON gl.rowno = glhi.rowno
WHERE 
        status = 'active'
        AND glhi.amount != 0.00
        AND glhi.effective BETWEEN '09-01-2017' AND '09-30-2017'
GROUP BY gl.rowno, gl.name, gl.no_, gl.branch, gl.fs, gl.cudic, gl.balance, glhi.effective, glhi.amount
ORDER BY gl.br, gl.name

This is an example of the current output I'm getting:

enter image description here

Edit: obvious answer would be to remove the glhi.amount from my GROUP BY clause, but when I do that I get this error:

Column 'glhi.amount' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Upvotes: 0

Views: 2219

Answers (5)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I think the best logic is:

sum(case when glhi.amount > 0 then glhi.amount else 0 end) as TotalPositive
sum(case when glhi.amount < 0 then glhi.amount else 0 end) as TotalNegative

This is called "conditional aggegation". The NULL comparison is not necessary; the else 0 handles that.

Upvotes: 1

rgen3
rgen3

Reputation: 907

Seems to be correct result, when you use aggregate functions like sum, avg, count etc. You have to group the values to make correct aggregation

In your case you are grouping the result by this condition GROUP BY gl.rowno, gl.name, gl.no_, gl.branch, gl.fs, gl.cudic, gl.balance, glhi.effective, glhi.amount

And your field glhi.amount has different values in table, so this query will give you more than one record

Moreover, note, that you should place your sum in this case inside sum function

CASE WHEN glhi.amount >0 THEN ISNULL(SUM(glhi.amount),0) END AS TotalPositive,
CASE WHEN glhi.amount <0 THEN ISNULL(SUM(glhi.amount),0) END AS TotalNegative

change to 

sum(case when glhi.amount > 0 then coalesce(glhi.amount, 0)) end as TotalPositive
sum(case when glhi.amount < 0 then coalesce(glhi.amount, 0)) end as TotalNegative

Upvotes: 2

tdm
tdm

Reputation: 87

Took 2 cases and put them within the SUM() clause, then was able to remove the GROUP BY amount and effective. Thank you everyone for your help

select DISTINCT
    gl.rowno,
    gl.br
    gl.fs 
    gl.cudic,
    gl.name
    gl.no_
    gl.balance 
    SUM(glhi.amount) AS Total,
    SUM(CASE WHEN glhi.amount >0 THEN glhi.amount END)AS TotalPositive,
    SUM(CASE WHEN glhi.amount <0 THEN glhi.amount END)AS TotalNegative
FROM gl
    INNER JOIN glhi ON gl.rowno = glhi.rowno
WHERE 
    status = 'active'
    AND glhi.amount != 0.00
    AND glhi.effective BETWEEN '09-01-2017' AND '09-30-2017'
    GROUP BY gl.rowno, gl.name, gl.no_, gl.br, gl.fs, gl.cudic, gl.balance
ORDER BY branch, name

Upvotes: 0

Chad Harrison
Chad Harrison

Reputation: 2858

If you have multiple glhi.amount per the other items grouped, it will return multiple rows. Try removing the the glhi.amount like so:

SELECT
        gl.rowno,
        gl.br
        gl.fs
        gl.cudic
        gl.name
        gl.no_
        gl.balance
        SUM(glhi.amount) AS Total,
        CASE WHEN glhi.amount >0 THEN ISNULL(SUM(glhi.amount),0) END AS TotalPositive,
        CASE WHEN glhi.amount <0 THEN ISNULL(SUM(glhi.amount),0) END AS TotalNegative
    FROM gl
        INNER JOIN glhi ON gl.rowno = glhi.rowno
    WHERE 
        status = 'active'
        AND glhi.amount != 0.00
        AND glhi.effective BETWEEN '09-01-2017' AND '09-30-2017'
        GROUP BY gl.rowno, gl.name, gl.no_, gl.branch, gl.fs, gl.cudic, gl.balance, glhi.effective
        ORDER BY gl.br, gl.name

Upvotes: 0

flyingfox
flyingfox

Reputation: 13506

According to the output data,you need to remove glhi.amount in GROUP BY

SELECT
        gl.rowno,
        gl.br
        gl.fs
        gl.cudic
        gl.name
        gl.no_
        gl.balance
        SUM(glhi.amount) AS Total,
        CASE WHEN glhi.amount >0 THEN ISNULL(SUM(glhi.amount),0) END AS TotalPositive,
        CASE WHEN glhi.amount <0 THEN ISNULL(SUM(glhi.amount),0) END AS TotalNegative
    FROM gl
        INNER JOIN glhi ON gl.rowno = glhi.rowno
    WHERE 
        status = 'active'
        AND glhi.amount != 0.00
        AND glhi.effective BETWEEN '09-01-2017' AND '09-30-2017'
        GROUP BY gl.rowno, gl.name, gl.no_, gl.branch, gl.fs, gl.cudic, gl.balance, glhi.effective
        ORDER BY gl.br, gl.name

Upvotes: 1

Related Questions