CODEWITHSUNDEEP
sqloracle-database
proR
proR

Reputation: 39

Calculating total items in each quarter by the last day of each month

I have a table that have multiple records of items with creation date over the years. I need to count all items that was created in last day of each month and then get the calculation for its quarter for each year by summing all items in its months. the table is as below:

item_code Creation_date location Item_type

for example: Jan 31 created items + Feb 28 created items + Mar 31 created items. year of 2023 = 500 and April 30 + May 31 + Jun 30 = 200 etc. Noticing that February is sometimes 28 and sometimes 29 depending on the year ( only count the items created on the last day of the month, not all those during that month)

the result is

total items Year Quarter
500 2023 1
200 2023 2

Thank you.

Upvotes: 0

Views: 52

Answers (1)

MT0
MT0

Reputation: 168106

You can use:

SELECT TO_CHAR(TRUNC(creation_date, 'Q'), 'YYYY') AS year,
       TO_CHAR(TRUNC(creation_date, 'Q'), 'Q') AS quarter,
       COUNT(item_code) AS total_items
FROM   table_name
WHERE  creation_date >= LAST_DAY(TRUNC(creation_date))
GROUP BY TRUNC(creation_date, 'Q')

Which, for the sample data:

CREATE TABLE table_name (item_code, Creation_date, location, Item_type) AS
SELECT 1, DATE '2023-01-01', 'x', 'y' FROM DUAL UNION ALL
SELECT 1, DATE '2023-01-31' + INTERVAL '1' HOUR, 'x', 'y' FROM DUAL UNION ALL
SELECT 1, DATE '2023-02-01' + INTERVAL '1' HOUR, 'x', 'y' FROM DUAL UNION ALL
SELECT 1, DATE '2023-02-28' + INTERVAL '1' HOUR, 'x', 'y' FROM DUAL UNION ALL
SELECT 1, DATE '2023-04-28' + INTERVAL '1' HOUR, 'x', 'y' FROM DUAL UNION ALL
SELECT 1, DATE '2023-04-30' + INTERVAL '1' HOUR, 'x', 'y' FROM DUAL UNION ALL
SELECT 1, DATE '2023-04-30' + INTERVAL '2' HOUR, 'x', 'y' FROM DUAL;

Outputs:

YEAR QUARTER TOTAL_ITEMS
2023 1 2
2023 2 2

fiddle

Upvotes: 2

Related Questions