locked
select with filtered sums RRS feed

  • Question

  • I have a table with the relevant columns:

    Table1 - Table1 -CustomerID,ChargeID,Amount,TransactionType
    Table2 -Customer -CusomerName

    I want a single select where I can return filtered sums

    Select t.CustomerID as ID,
    c.CustomerName,
    SUM(t2.Amount)  as Charge,
    SUM(t3.Amount)  as Payment

    join Customer c on c.CustomerID = t.CustomerID
    join Table1 t2 on t1.CustomerID = t2.CustomerID and t2.TransactionType = 'Charge'
    join Table1 t3 on t1.CustomerID = t2.CustomerID and t2.TransactionType = 'Payment'


    From Table1 t

    Is there a better way?

    Thanks,
    Rick

    Friday, March 12, 2010 2:33 PM

Answers

  • Something like this ....


    Select t.CustomerID as ID,
    c.CustomerName,
    SUM(case when t2.TransactionType = 'Charge' then t2.Amount else 0 end)  as Charge,
    SUM(case when t2.TransactionType = 'Payment' then t2.Amount else 0 end)  as Payment
    From Customer c on c.CustomerID = t.CustomerID join Table1 t2 on t1.CustomerID = t2.CustomerID where t2.TransactionType in('Charge','Payment') group by t.CustomerID ,c.CustomerName



    Dave Ballantyne ---- http://sqlandthelike.blogspot.com
    Friday, March 12, 2010 2:45 PM

All replies

  • Something like this ....


    Select t.CustomerID as ID,
    c.CustomerName,
    SUM(case when t2.TransactionType = 'Charge' then t2.Amount else 0 end)  as Charge,
    SUM(case when t2.TransactionType = 'Payment' then t2.Amount else 0 end)  as Payment
    From Customer c on c.CustomerID = t.CustomerID join Table1 t2 on t1.CustomerID = t2.CustomerID where t2.TransactionType in('Charge','Payment') group by t.CustomerID ,c.CustomerName



    Dave Ballantyne ---- http://sqlandthelike.blogspot.com
    Friday, March 12, 2010 2:45 PM
  • You can do this using subqueries, as follows

    DECLARE @Table1 TABLE(CustomerID INT,ChargeID INT,Amount DECIMAL(5,2),TransactionType VARCHAR(20))
    INSERT INTO @Table1
    SELECT 1,123,32.22,'Charge' UNION ALL SELECT 2,123,12.22,'Payment' UNION ALL
    SELECT 3,123,132.22,'Charge' UNION ALL SELECT 3,124,122.05,'Payment' UNION ALL
    SELECT 3,112,11.09,'Payment' UNION ALL SELECT 4,22,123.89,'Charge' 
    DECLARE @Customer TABLE(CustomerID INT,CustomerName VARCHAR(50))
    INSERT INTO @Customer
    SELECT 1,'John' UNION ALL SELECT 2,'Mike' UNION ALL SELECT 3,'Sam' UNION ALL SELECT 4,'Mohammad'
    
    
    SELECT t.CustomerID,c.CustomerName,
    		(SELECT SUM(Amount) 
    			FROM @Table1 
    			WHERE CustomerID = t.CustomerID
    				AND TransactionType = 'Charge') AS [Charge],
    		(SELECT SUM(Amount) 
    			FROM @Table1 
    			WHERE CustomerID = t.CustomerID
    				AND TransactionType = 'Payment') AS [Payment]
    FROM @Table1 t
    	JOIN @Customer C ON t.CustomerID = C.CustomerID

    Abdallah El-Chal, PMP, ITIL, MCTS
    Friday, March 12, 2010 2:54 PM
  • Performance wise Dave's solution is going to be much better.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, March 12, 2010 2:56 PM
  • Naomi,

    Did you notice that Dave's solution has many syntax errors and it will not run?

    Abdallah El-Chal, PMP, ITIL, MCTS
    Friday, March 12, 2010 2:57 PM
  • Naomi,

    Did you notice that Dave's solution has many syntax errors and it will not run?

    Abdallah El-Chal, PMP, ITIL, MCTS

    Should now be correct,  though i readily admit i havent tested it.
    Sound theory though :)


    Dave Ballantyne ---- http://sqlandthelike.blogspot.com
    Friday, March 12, 2010 3:05 PM
  • No, didn't notice - I just took a quick glance :)
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, March 12, 2010 3:07 PM