locked
SELECT query, 2 SUM functions with different conditions RRS feed

  • 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!


     

    Wednesday, August 28, 2013 10:03 AM

Answers

  • 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.

    • Proposed as answer by Kalman Toth Wednesday, August 28, 2013 10:19 AM
    • Marked as answer by juve1119 Wednesday, August 28, 2013 12:45 PM
    Wednesday, August 28, 2013 10:15 AM
  • 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


    • Marked as answer by juve1119 Wednesday, August 28, 2013 12:46 PM
    Wednesday, August 28, 2013 10:18 AM

All replies

  • 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.

    • Proposed as answer by Kalman Toth Wednesday, August 28, 2013 10:19 AM
    • Marked as answer by juve1119 Wednesday, August 28, 2013 12:45 PM
    Wednesday, August 28, 2013 10:15 AM
  • 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


    • Marked as answer by juve1119 Wednesday, August 28, 2013 12:46 PM
    Wednesday, August 28, 2013 10:18 AM
  • 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 ;


    • Proposed as answer by scott_morris-ga Wednesday, August 28, 2013 12:40 PM
    Wednesday, August 28, 2013 10:18 AM
  • 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.
    Wednesday, August 28, 2013 12:40 PM
  • Got it, thank you!
    Wednesday, August 28, 2013 12:45 PM