Group by with case statement

Question

5.00/5 (1 vote)

See more:

Dear All,

I have below mentioned query I have used case statement to sum balance based on specific criterion ,I am getting error of group by while executing the query.

any kind of help is appreciated,

What I have tried:

select brcode,count(Acno),sum(balance) as Outstanding, case when NPACode>0 then sum(balance) else  0 end as NPA from #temploan 
group by BrCode order by BrCode

Posted 30-Aug-20 7:23am

Hemil Gandhi

Updated 31-Aug-20 6:57am

Add a Solution

3 solutions

Solution 1

Though you have not shared the error, believe the error would be because you have grouped by only brcode and your select has npaCode being used not in any aggregate functions.

There would be different solves for it, one of them being adding NPACode to Group by clause.
Try:

SQL

select 
    brcode,
    count(Acno),
    sum(balance) as Outstanding, 
    case 
        when NPACode>0 then sum(balance) 
        else  0 
    end as NPA 
from 
    #temploan 
group by 
    BrCode, NPACode 
order by 
    BrCode

More detailed discussions and other ways to handle it: sql - GROUP BY + CASE statement[^]

Posted 30-Aug-20 9:02am

Sandeep Mewara

Updated 30-Aug-20 22:01pm

v4

Comments

Jörgen Andersson 31-Aug-20 3:19am

Well, that probably won't work.
You can't use NPA in the Group by if it's created in the Select.

Sandeep Mewara 31-Aug-20 4:02am

Apologies. I wrote NPACode and then changed to NPA thinking otherwise. Having NPACode would work out as valid query in groupby. Given NPA is created in there, it does not affect group by.

I see your solution and yes its another valid way as shared in the discussion link above.

Thanks for checking and sharing man. :thumsup:

Jörgen Andersson 31-Aug-20 4:23am

Well you won't get an error, but you will get a row for each value of NPACode, not just for NPACode > 0

Sandeep Mewara 31-Aug-20 4:45am

True. That would attribute to the intent of the query. :thumbsup:

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jörgen Andersson · Accepted Answer · 2020-08-30T21:22:00

You also need to group by "NPACode > 0"

The easiest way should be:

SQL

SELECT  brcode
       ,count(Acno)
       ,sum(balance) AS Outstanding
       ,CASE 
            WHEN NPACode > 0
                THEN sum(balance)
            ELSE 0
        END AS NPA
FROM    #temploan
GROUP BY BrCode
        ,CASE WHEN NPACode > 0 THEN 1 ELSE 0 END
ORDER BY BrCode

<edit>
I'm wondering about the purpose of Outstanding vs NPA, they would be the same if NPACode is > 0

I have a feeling this is what you want:

SQL

SELECT  brcode
       ,count(Acno)
       ,sum(balance) AS Outstanding
       ,SUM(
            CASE 
                WHEN NPACode > 0
                    THEN balance
                ELSE 0
            END
            ) AS NPA
FROM    #temploan
GROUP BY BrCode
ORDER BY BrCode

</edit>

Hemil Gandhi · Accepted Answer · 2020-08-31T06:58:00

Solution 3

select brcode,count(Acno),sum(balance) as Outstanding, SUM(case when NPACode>0 then balance else  0 end) as NPA from #temploan 
group by BrCode order by BrCode

Posted 31-Aug-20 6:58am

Hemil Gandhi

Comments

ZurdoDev 31-Aug-20 15:41pm

Nope

Hemil Gandhi 31-Aug-20 23:37pm

This query solved my problem.

ZurdoDev 1-Sep-20 7:05am

No it did not. This is the same as in your OP that had a problem.

Maciej Los 31-Aug-20 15:51pm

This is an exact copy of solution #2 by Jörgen Andersson. Do NOT post such of content as your own solution.