Forum Stats

  • 3,837,059 Users
  • 2,262,222 Discussions
  • 7,900,194 Comments

Discussions

Aggregating rows to form a report

The_Cute_DBA
The_Cute_DBA Member Posts: 61 Blue Ribbon

Hello Experts,

How can I aggregate this CUSTOMERS table data using SQL code:

CustomerName CustomerID Amount
Maria         101        25
Maria         101        35
Maria         101        45
John          202        10
John          202        10
John          202        10
James         303        70

To make it look like this:

CustomerName CustomerID Amount
Maria          101       105
John           202       30
James          303       70

Best Answers

  • User_VWCI5
    User_VWCI5 Member Posts: 3 Employee
    Answer ✓

    Hey there! Try this one:


    SQL> select customername, min(CUSTOMERID), sum(amount)
     2 from customers
     3 group by CUSTOMERNAME;
    
    CUSTOMERNAME          MIN(CUSTOMERID) SUM(AMOUNT)
    ------------------------------ --------------- -----------
    Maria                   101     105
    James                   303     70
    John                    202     30
    


  • BluShadow
    BluShadow Member, Moderator Posts: 42,110 Red Diamond
    Answer ✓

    It would probably be better grouping on customerid instead of name, just in case there is more than 1 customer with the same name.

    select max(customername) as customername
          ,customerid
          ,sum(amount) as amount
    from customers
    group by customerid;
    


  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,212 Red Diamond
    Answer ✓

    Hi, @The_Cute_DBA

    Or you could group by both:

    SELECT   customername, customerid
    ,	 SUM (amount)	AS total_amount
    FROM 	 customers
    GROUP BY customername, customerid
    ORDER BY customername, customerid -- or whatever you want
    ;
    

    All three solutions get the same results with the sample data you posted. All three solutions could give different results with different data.

Answers

  • User_VWCI5
    User_VWCI5 Member Posts: 3 Employee
    Answer ✓

    Hey there! Try this one:


    SQL> select customername, min(CUSTOMERID), sum(amount)
     2 from customers
     3 group by CUSTOMERNAME;
    
    CUSTOMERNAME          MIN(CUSTOMERID) SUM(AMOUNT)
    ------------------------------ --------------- -----------
    Maria                   101     105
    James                   303     70
    John                    202     30
    


  • BluShadow
    BluShadow Member, Moderator Posts: 42,110 Red Diamond
    Answer ✓

    It would probably be better grouping on customerid instead of name, just in case there is more than 1 customer with the same name.

    select max(customername) as customername
          ,customerid
          ,sum(amount) as amount
    from customers
    group by customerid;
    


  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,212 Red Diamond
    Answer ✓

    Hi, @The_Cute_DBA

    Or you could group by both:

    SELECT   customername, customerid
    ,	 SUM (amount)	AS total_amount
    FROM 	 customers
    GROUP BY customername, customerid
    ORDER BY customername, customerid -- or whatever you want
    ;
    

    All three solutions get the same results with the sample data you posted. All three solutions could give different results with different data.

  • The_Cute_DBA
    The_Cute_DBA Member Posts: 61 Blue Ribbon

    Thank you guys. This is very helpful.