CODEWITHSUNDEEP
sqloracle-database
Mike
Mike

Reputation: 25

SQL Statement To Group Different Date Ranges as New Columns

SQL beginner here. Looking at a table of items in an Oracle DB and wanted to export items by year (each in a separate column), group them by a userid, and then sum a total field.

I can export them individually with date ranges like

WHERE DATE > '01-JAN-13'
AND DATE < '31-DEC-13'

My table 'CUSTOMER_ORDERS' looks like this Here is how my table looks

Customer Name | Customer ID | Date | Sale
_________________________________________
Customer 1 | CUS01 | 05-JAN-13 | 110.00
Customer 2 | CUS02 | 06-JAN-11 | 110.00
Customer 3 | CUS03 | 07-JAN-12 | 70.00
Customer 1 | CUS01 | 05-JAN-12 | 10.00
Customer 2 | CUS02 | 05-JAN-11 | 210.00

Ideally I want to export something like this

Customer Name | Customer ID | 2011 Total | 2012 Total | 2013 Total
_________________________________________
Customer 1 | CUS01 | 0   | 10  | 110
Customer 2 | CUS02 | 320 | 0   |  0
Customer 3 | CUS03 | 0   | 70  |  0

I'm sure this is super simple, I just can't figure out the right way to do it.

Upvotes: 0

Views: 3386

Answers (2)

Taryn
Taryn

Reputation: 247650

You can use an aggregate function with a CASE expression to PIVOT the data from rows into columns:

select 
  CustomerName,
  CustomerID, 
  sum(case when to_char(dt, 'YYYY') = 2011 then Sale else 0 end) Total_2011,
  sum(case when to_char(dt, 'YYYY') = 2012 then Sale else 0 end) Total_2012,
  sum(case when to_char(dt, 'YYYY') = 2013 then Sale else 0 end) Total_2013
from CUSTOMER_ORDERS
group by CustomerName, CustomerID;

See SQL Fiddle with Demo.

Depending on your version of Oracle, you might be able to use the PIVOT function if you are using Oracle 11g+:

select *
from
(
  select CustomerName, CustomerId, 
    'Total_'||to_char(dt, 'YYYY') year, sale
  from CUSTOMER_ORDERS
) 
pivot
(
  sum(sale)
  for year in ('Total_2011', 'Total_2012', 'Total_2013')
);

See SQL Fiddle with Demo

Upvotes: 5

Nicholas Carey
Nicholas Carey

Reputation: 74197

Use the power of self-joins to subset the data in the way you need. Try something like

select c.ID , c.Name , sum(c2011.Sale) , sum(c2012.Sale) , sum( c2013.Sale )
from      ( select distinct c.ID , c.Name from customer_order ) c
left join customer_order c2011 on c2011.id = c.id and year(c.Date) = 2011
left join customer_order c2012 on c2012.id = c.id and year(c.Date) = 2012
left join customer_order c2013 on c2013.id = c.id and year(c.Date) = 2013
group by c.ID , c.Name
order by c.ID , c.Name

To get the desired result. Alternatively...

select c.ID , c.Name ,
       sum(case when year(c.Date) = 2011 then c.Sale else 0 end) ,
       sum(case when year(c.Date) = 2012 then c.Sale else 0 end) ,
       sum(case when year(c.Date) = 2013 then c.Sale else 0 end)
from customer_order c
group by c.ID , c.Name
order by c.ID , c.Name

Upvotes: 0

Related Questions