Gouping in the following CAST((INT_ARREARS+PRIN_ARREARS)/DEDUCT as INT) as COMP_ARREARS

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

SELECT CAST((INT_ARREARS+PRIN_ARREARS)/DEDUCT AS INT) as COMP_ARREARS ,
SUM((INT_ARREARS+PRIN_ARREARS)) AS SUM_INT_PRIN
FROM HLOANS where DEDUCT >0 and INT_ARREARS+PRIN_ARREARS>0
AND CAST((INT_ARREARS+PRIN_ARREARS)/DEDUCT AS INT) >0
AND CMONTH=@ZMONTH AND CYEAR=@ZYEAR
GROUP BY CAST((INT_ARREARS+PRIN_ARREARS)/DEDUCT AS INT)
ORDER BY CAST((INT_ARREARS+PRIN_ARREARS)/DEDUCT AS INT)

Need to sum on the following 'CAST((INT_ARREARS+PRIN_ARREARS)/DEDUCT AS INT) as COMP_ARREARS'

such that output 1,2,3 (i.e. 1-3) will be summed as one

4,5,6 (i.e 4-6) will be summed as another

7-8-9 (i.e 7-9) will be summed as another

CAST((INT_ARREARS+PRIN_ARREARS)/DEDUCT AS INT) as COMP_ARREARS returns integers

How do I accomplish this ?

Thanks

What I have tried:

Tried my own codes and other the internet

Posted 18-Apr-16 20:17pm

Member 10744248

Updated 19-Apr-16 12:06pm

Add a Solution

Comments

Tomas Takac 19-Apr-16 2:35am

What is the problem? Do you get an error? Or the output is different than you expected? You should share some sample input data and actual and expected output.

1 solution

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)

CHill60 · Accepted Answer · 2016-04-19T12:06:00

Use a combination of a Common Table Expression (CTE), BETWEEN and CASE ...

SQL

;with cte as
(
	SELECT CAST((INT_ARREARS+PRIN_ARREARS)/DEDUCT AS INT) as COMP_ARREARS ,
	 SUM((INT_ARREARS+PRIN_ARREARS)) AS SUM_INT_PRIN 
	 FROM HLOANS where DEDUCT >0 and INT_ARREARS+PRIN_ARREARS>0 
	 AND CAST((INT_ARREARS+PRIN_ARREARS)/DEDUCT AS INT) >0
	 AND CMONTH=@ZMONTH AND CYEAR=@ZYEAR
	 GROUP BY CAST((INT_ARREARS+PRIN_ARREARS)/DEDUCT AS INT) 
)
SELECT CASE WHEN COMP_ARREARS BETWEEN 1 AND 3 THEN CAST('1-3' AS Varchar(8))
			WHEN COMP_ARREARS BETWEEN 4 AND 6 THEN '4-6'
			WHEN COMP_ARREARS BETWEEN 7 AND 9 THEN '7-9'
			ELSE 'The Rest' END AS [Grouping],
		SUM(SUM_INT_PRIN) AS SumOfGroup
FROM cte
GROUP BY CASE WHEN COMP_ARREARS BETWEEN 1 AND 3 THEN CAST('1-3' AS Varchar(8))
			WHEN COMP_ARREARS BETWEEN 4 AND 6 THEN '4-6'
			WHEN COMP_ARREARS BETWEEN 7 AND 9 THEN '7-9'
			ELSE 'The Rest' END
ORDER BY 1

Things to note:
- The body of the CTE (cte) is your original query (minus the ORDER BY which is not allowed)
- The first column in the SELECT from the CTE uses CASE and BETWEEN to get the "title" of the groupings we need.
- The GROUP BY clause effectively repeats that grouping title
- I couldn't be bothered to repeat it all again for the ORDER BY clause so I used the trick of saying "order by the first column" ... ORDER BY 1
- It would be great if I could have just said GROUP BY 1 as well but that generates an error :(

Quote:
Each GROUP BY expression must contain at least one column that is not an outer reference.

- I didn't have to use a CTE ... I could have written something like

SQL

SELECT CASE WHEN CAST((INT_ARREARS+PRIN_ARREARS)/DEDUCT AS INT) BETWEEN 1 AND 3 THEN CAST('1-3' AS Varchar(8))
			WHEN CAST((INT_ARREARS+PRIN_ARREARS)/DEDUCT AS INT) BETWEEN 4 AND 6 THEN '4-6'
			WHEN CAST((INT_ARREARS+PRIN_ARREARS)/DEDUCT AS INT) BETWEEN 7 AND 9 THEN '7-9'
			ELSE 'The Rest' END AS [Grouping],
		SUM((INT_ARREARS+PRIN_ARREARS)) AS SumOfGroup
	FROM HLOANS where DEDUCT >0 and INT_ARREARS+PRIN_ARREARS>0 
	 AND CAST((INT_ARREARS+PRIN_ARREARS)/DEDUCT AS INT) >0
	 AND CMONTH=@ZMONTH AND CYEAR=@ZYEAR
GROUP BY CASE WHEN CAST((INT_ARREARS+PRIN_ARREARS)/DEDUCT AS INT) BETWEEN 1 AND 3 THEN CAST('1-3' AS Varchar(8))
			WHEN CAST((INT_ARREARS+PRIN_ARREARS)/DEDUCT AS INT) BETWEEN 4 AND 6 THEN '4-6'
			WHEN CAST((INT_ARREARS+PRIN_ARREARS)/DEDUCT AS INT) BETWEEN 7 AND 9 THEN '7-9'
			ELSE 'The Rest' END
ORDER BY 1

But apart from the fact it is more difficult to read, look at the number of times it is having to recalculate the field that eventually becomes COMP_ARREARS!