Extract Quarterly Profit from Daily Data

Question

I have the following columns in my table:

Date: Formatted as YYYY-MM-DD
Revenue: Integer representing revenue for that day
Cost: Integer representing costs for that day

I need to find a way to group the dates into quarters and find out profits for that quarter. Profit would simply be sum of all the differences between revenue and costs for each day in that quarter (Omitting quarters not listed in the original table):

Q1: Jan 1 - Mar 31

Q2: Apr 1 - June 30

Q3: July 1 - Sept 30

Q4: Oct 1 - Dec 31

Lets say the original table is the following:

Date	Revenue	Cost
2021-02-05	100	10
2021-02-06	50	10
2021-12-05	0	10
2021-12-06	0	10

I would want to generate the following table:

Year	Quarter	Profit
2021	Q1	130
2021	Q4	-20

Does this answer your question? [Postgres group by quarter](https://stackoverflow.com/questions/37071631/postgres-group-by-quarter) — acrlnb, Oct 06 '21 at 23:53
Unfortunately no, using the answer on that page causes two separate daily entries from the same quarter to be displayed as two separate entries in the result table, when I need them to show up as 1 — DataScience99, Oct 07 '21 at 00:05

score 1 · Accepted Answer · answered Oct 07 '21 at 00:06

You may use the following:

SELECT
    EXTRACT(YEAR FROM "Date") as "Year",
    'Q'||EXTRACT(QUARTER FROM "Date") as "Quarter",
    SUM("Revenue"-"Cost") as "Profit"
FROM
    my_table
GROUP BY
    1,2
ORDER BY
    1,2;

Year	Quarter	Profit
2021	Q1	130
2021	Q4	-20

View working demo on DB Fiddle

Let me know if this works for you.

Extract Quarterly Profit from Daily Data

1 Answers1