Forum Stats

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

Discussions

Categories

Aggregating rows to form a report

The_Cute_DBA
The_Cute_DBA Member Posts: 61 Blue Ribbon
in SQL & PL/SQL

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
· Share on TwitterShare on Facebook

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


    · Share on TwitterShare on Facebook
  • 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;


    · Share on TwitterShare on Facebook
  • 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.

    · Share on TwitterShare on Facebook

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


    · Share on TwitterShare on Facebook
  • 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;


    · Share on TwitterShare on Facebook
  • 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.

    · Share on TwitterShare on Facebook
  • The_Cute_DBA
    The_Cute_DBA Member Posts: 61 Blue Ribbon

    Thank you guys. This is very helpful.

    · Share on TwitterShare on Facebook