I have the following columns in my table:

1. Date: Formatted as YYYY-MM-DD

2. Revenue: Integer representing revenue for that day

3. 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:

I would want to generate the following table:

CodePudding user response：

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;

View working demo on DB Fiddle

Let me know if this works for you.