aggregate function applying to same column with different where conditions

Question

0.00/5 (No votes)

See more:

suppose i have employee table
emp(empid,scode,amt) -> (1,'e',1000),(1,'d',500)
e stands for earning and d stands for deductions. i need a query in such a way that sums of amt for both scode for a particular empid

SQL

SELECT sum( amt ) FROM emp WHERE scode = 'e' AND empid =1

will give only total amount for employee with scode=e.
i want both.how the query will be?

Posted 12-Mar-13 1:46am

DARSANA K

Updated 12-Mar-13 1:52am

gvprabu

v2

Add a Solution

Comments

Davidduraisamy 12-Mar-13 7:49am

hey wat u r asking

Davidduraisamy 12-Mar-13 7:50am

If u want both means in where condition dont use particular condition

Davidduraisamy 12-Mar-13 7:56am

SELECT sum( amt ) FROM emp WHERE empid =1

3 solutions

Solution 1

Try like this....

Use like this

SQL

SELECT SUM(ISNULL(amt,0)) FROM emp WHERE scode IN('e','d') AND empid =1

Posted 12-Mar-13 1:58am

gvprabu

Updated 12-Mar-13 2:09am

v3

Comments

Davidduraisamy 12-Mar-13 7:59am

Suppose if scode has more code for particular id then this wont work...right...

DARSANA K 13-Mar-13 5:16am

yes its what i mean

gvprabu 12-Mar-13 8:00am

he asked only e, d right :-)

Karthik Harve 12-Mar-13 8:06am

you have not used CASE statement.

DARSANA K 12-Mar-13 8:01am

it wont give correct answer

gvprabu 12-Mar-13 8:03am

Exactly Wht u want..? if you remove the WHERE Condition.. then Ok right

Solution 2

try this ..

SELECT sum( amt ) FROM emp WHERE  empid =1 group by scode

hope it will help

update query...

SQL

SELECT distinct  (select sum(amt) FROM emp where empid='1' and scode ='e' ) as totalearnings,
distinct (select sum(amount)  from emp where empid='1' and scode ='d') as totaldeductions
FROM emp;

Posted 12-Mar-13 4:08am

Pallavi Waikar

Updated 13-Mar-13 0:34am

v2

Comments

Davidduraisamy 13-Mar-13 5:18am

send sample output

DARSANA K 13-Mar-13 5:26am

empid scode amt date
-----------------------------------------------
1 e 1000 2013-03-12
1 a 100 2013-03-12
1 d 500 2013-03-12
1 e 1000 2013-03-12
1 e 500 2013-03-12
suppose this is the table

and o/p like
totalearnings|totaldeductions
---------------------------------
2500 | 500

Pallavi Waikar 13-Mar-13 6:37am

don't ask anyone like this send me answer it will affect u by avoiding other to help u...check out updated query...not sure but try...and test for more values.

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 · 2013-03-13T09:01:00

Solution 3

select  sum(case scode when 'e' then amt else 0 end) as totalearnings
        sum(case scode when 'd' then amt else 0 end) as totaldecuctions
from    emp
where   empid = 1

Posted 13-Mar-13 9:01am

Jörgen Andersson