Click here to Skip to main content
15,843,200 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
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
Updated 31-Aug-20 7:57am

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[^]
 
Share this answer
 
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:
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>
 
Share this answer
 
v3
Comments
Maciej Los 31-Aug-20 4:40am    
5ed!
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
 
Share this answer
 
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.
Hemil Gandhi 31-Aug-20 23:37pm    
noted sir

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900