SELECT name, SUM(salary) as salary , SUM(CASE WHEN convert(varchar(30), salary, 1) >'25000' THEN 'YES' ELSE 'NO' END) as BONUS from EMPLOYEE GROUP by NAME, salary

Question

0.00/5 (No votes)

See more:

SELECT Name,SUM(salary) AS salary ,SUM(CASE  WHEN convert(varchar(30),salary,1) >'25000' THEN 'YES' ELSE 'NO' END) AS
BONUS FROM EMPLOYEE GROUP BY NAME,salary

I am using above query for getting name,sum of salary and also want to print the Yes when salary greater than 20000 and No when salary less than 20000 .

note : if same employee get two times salary then name will display only one time with total of two time salary .

What I have tried:

Operand data type varchar is invalid for sum operator.

<pre>SELECT Name,SUM(salary) AS salary ,SUM(CASE  WHEN convert(varchar(30),salary,1) >'25000' THEN 'YES' ELSE 'NO' END) AS
BONUS FROM EMPLOYEE GROUP BY NAME,salary

I am using above query for getting name,sum of salary and also want to print the Yes when salary greater than 20000 and No when salary less than 20000 .

note : if same employee get two times salary then name will display only one time with total of tow time salary .

Posted 27-Jan-17 0:12am

Member 10038070

Updated 27-Jan-17 0:30am

v2

Add a Solution

Comments

José Amílcar Casimiro 27-Jan-17 6:16am

Do you think it makes sense to sum 'YES' and 'NO'? Additionally you have a problem with group by.

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)

OriginalGriff · Answer 1 · 2017-01-27T00:30:00

You can't add strings: If I cut out the other bits it more obvious what you have done:

SELECT ... ,SUM(CASE WHEN ... THEN 'YES' ELSE 'NO' END) AS BONUS ...

You are trying to sum a series of "Yes" and "No" strings, which just won't work: the SUM function expects a numeric value. Probably what you want to do is is a JOIN to combine your GROUPED data and add info to it - but I suspect that your GROUP BY clause has too many columns listed as well. I think you probably need to rethink what you are trying to do as this looks as bit "throw it together and hope" instead of considered SQL code.

To add to that, why are you converting a numeric value to a string and then trying to compare it to a number in a string? String comparisons are performed character by character with the result of the whole comparison being decided by the first different character. So '3' is greater than '25000', and '100000000' is less than '25000'.
Always compare numbers as numbers!