聚合函数应用于具有不同where条件的相同列 [英] aggregate function applying to same column with different where conditions

问题描述

假设我有员工表

emp（empid，scode，amt） - > （1，''e''，1000），（1，'''，500）

e代表收益，d代表扣除。我需要一个查询，以便两个sct的特定empid的总和为

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

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

只会为scode = e的员工提供总金额。

i想要两者。查询将是什么？

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

推荐答案

试试这样....



像这样使用

Try like this....

Use like this

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

试试这个..

try this ..

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

希望它能帮助



更新查询。 ..

hope it will help

update query...

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;

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

这篇关于聚合函数应用于具有不同where条件的相同列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！