CODEWITHSUNDEEP
sqloracle-databaseaggregate-functions
MehranYazdizadeh
MehranYazdizadeh

Reputation: 105

How to aggregate based on various conditions

lets say I have a table which stores itemID, Date and total_shipped over a period of time:

ItemID | Date      | Total_shipped
__________________________________
  1    | 1/20/2000 |   2
  2    | 1/20/2000 |   3
  1    | 1/21/2000 |   5
  2    | 1/21/2000 |   4
  1    | 1/22/2000 |   1
  2    | 1/22/2000 |   7
  1    | 1/23/2000 |   5
  2    | 1/23/2000 |   6

Now I want to aggregate based on several periods of time. For example, I Want to know how many of each item was shipped every two days and in total. So the desired output should look something like:

ItemID | Jan20-Jan21 | Jan22-Jan23 | Jan20-Jan23
_____________________________________________
  1    |  7          |  6          |  13
  2    |  7          |  13         |  20

How do I do that in the most efficient way I know I can make three different subqueries but I think there should be a better way. My real data is large and there are several different time periods to be considered i. e. in my real problem I want the shipped items for current_week, last_week, two_weeks_ago, three_weeks_ago, last_month, two_months_ago, three_months_ago so I do not think writing 7 different subqueries would be a good idea. Here is the general idea of what I can already run but is very expensive for the database

WITH 
sq1 as (
SELECT ItemID, sum(Total_shipped) sum1
FROM table
WHERE Date BETWEEN '1/20/2000' and '1/21/2000'
GROUP BY ItemID),
sq2 as (
SELECT ItemID, sum(Total_Shipped) sum2
FROM table
WHERE Date BETWEEN '1/22/2000' and '1/23/2000'
GROUP BY ItemID),
sq3 as(
SELECT ItemID, sum(Total_Shipped) sum3
FROM Table
GROUP BY ItemID)
SELECT ItemID, sq1.sum1, sq2.sum2, sq3.sum3
FROM Table 
JOIN sq1 on Table.ItemID = sq1.ItemID
JOIN sq2 on Table.ItemID = sq2.ItemID
JOIN sq3 on Table.ItemID = sq3.ItemID

Upvotes: 2

Views: 58

Answers (2)

Popeye
Popeye

Reputation: 35900

I dont know why you have tagged this question with multiple database.

Anyway, you can use conditional aggregation as following in oracle:

select
    item_id, 
    sum(case when "date" between date'2000-01-20' and date'2000-01-21' then total_shipped end) as "Jan20-Jan21",
    sum(case when "date" between date'2000-01-22' and date'2000-01-23' then total_shipped end) as "Jan22-Jan23",
    sum(case when "date" between date'2000-01-20' and date'2000-01-23' then total_shipped end) as "Jan20-Jan23"
from my_table
group by item_id

Cheers!!

Upvotes: 2

klin
klin

Reputation: 121604

Use FILTER:

select
    item_id, 
    sum(total_shipped) filter (where date between '2000-01-20' and '2000-01-21') as "Jan20-Jan21",
    sum(total_shipped) filter (where date between '2000-01-22' and '2000-01-23') as "Jan22-Jan23",
    sum(total_shipped) filter (where date between '2000-01-20' and '2000-01-23') as "Jan20-Jan23"
from my_table
group by 1

 item_id | Jan20-Jan21 | Jan22-Jan23 | Jan20-Jan23
---------+-------------+-------------+-------------
       1 |           7 |           6 |          13
       2 |           7 |          13 |          20
(2 rows)

Db<>fiddle.

Upvotes: 1

Related Questions