SELECT query, 2 SUM functions with different conditions

Question

Hello,

in the same SELECT query i need to get values from 2 SUM columns but having different WHERE conditions.

Example:

SELECT name, SUM(bill) as 'SumNo1',SUM(invoice) as 'SumNo2' from table where client_date>'2013-01-01' group by name ;

I need to get 'SumNo2' based on a different condition, like client_date <'2013-01-01'. Is this possible in the same query?

Thank you!

 

Answer 1

Try the below:

Drop table [Table]
create Table [Table](name varchar(100),Bill int,invoice int, Client_date date)

Insert into [Table] Select 'SQL',1,1,'2013-01-02'
Insert into [Table] Select 'SQL',22,2,'2013-01-03'
Insert into [Table] Select 'SQL',4,1,'2012-12-30'
Insert into [Table] Select 'SQL',4,5,'2012-12-29'

SELECT name, 
(Select SUM(bill) as 'SumNo1' From  [table] B1 where client_date>'2013-01-01' and A.name = B1.name),
(Select SUM(invoice) as 'SumNo2' From  [table] C1 where client_date<'2013-01-01' and A.name = C1.name )
from [table] A
Group by name

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 2

SELECT

distinct t1.name,

(select sum(t2.bill) from <table> t2 where t2.name = t1.name and <where-clause>),

(select sum(t3.invoice) from <table> t3 where t3.name = t1.name and <other-where-clause>)

from <table> t1

Answer 3

Try this:

SELECT name, SUM(CASE WHEN client_date < '2013-01-01' THEN bill ELSE 0 END) as 'SumNo1',SUM(CASE WHEN client_date > '2013-01-01'  THEN invoice ELSE 0 END) as 'SumNo2' from table group by name ;

Answer 4

Logically, the concept is that you select the superset of rows and use case expressions within your sum aggregates to limit the rows included in the aggregate.  AbhishekJain86 posted the solution I would use.  To be safe, you should compare the execution plans to determine which is more efficient.

Answer 5

Got it, thank you!