CODEWITHSUNDEEP
sqlpostgresql
Michi
Michi

Reputation: 5481

Assign total value of month to each day of month

DB-Fiddle

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    country VARCHAR(255),
    sales_date DATE,
    sales_volume DECIMAL,
    fix_costs DECIMAL
);

INSERT INTO sales
(country, sales_date, sales_volume, fix_costs
)
VALUES 

('DE', '2020-01-03', '500', '0'),
('FR', '2020-01-03', '350', '0'),
('None', '2020-01-31', '0', '2000'),

('DE', '2020-02-15', '0', '0'),
('FR', '2020-02-15', '0', '0'),
('None', '2020-02-29', '0', '5000'),

('DE', '2020-03-27', '180', '0'),
('FR', '2020-03-27', '970', '0'),
('None', '2020-03-31', '0', '4000');

Expected Result:

sales_date    |  country    |  sales_volume     |     fix_costs
--------------|-------------|-------------------|-----------------
2020-01-03    |     DE      |         500       |       2000
2020-01-03    |     FR      |         350       |       2000
2020-02-15    |     DE      |           0       |       5000
2020-02-15    |     FR      |           0       |       5000
2020-03-27    |     DE      |         180       |       4000
2020-03-27    |     FR      |         970       |       4000

As you can see in my table I have a total of fix_costs assigned to the last day of each month.

In my results I want to assign this total of fix_costs to each day of the month.
Therefore, I tried to go with this query:

SELECT
s.sales_date, 
s.country,
s.sales_volume,
f.fix_costs
FROM sales s

JOIN

  (SELECT 
  ((date_trunc('MONTH', sales_date) + INTERVAL '1 MONTH - 1 DAY')::date) AS month_ld,
  SUM(fix_costs) AS fix_costs
  FROM sales
  WHERE country = 'None'
  GROUP BY month_ld) f ON f.month_ld = LAST_DAY(s.sales_date)

WHERE country <> 'None'
GROUP BY 1,2,3;

For this query I get an error on the LAST_DAY(s.sales_date) since this expression does not exist in PostgresSQL.
However, I have no clue how I can replace it correctly in order to get the expected result.

Can you help me?

(MariaDB Fiddle as comparison)

Upvotes: 1

Views: 51

Answers (2)

S-Man
S-Man

Reputation: 23766

demos:db<>fiddle

SELECT
    s1.sales_date,
    s1.country,
    s1.sales_volume,
    s2.fix_costs
FROM sales s1
JOIN sales s2 ON s1.country <> 'None' AND s2.country = 'None' 
    AND date_trunc('month', s1.sales_date) = date_trunc('month', s2.sales_date)

You need a natural self-join. Join conditions are:

  1. First table without None records (s1.country <> 'None')
  2. Second table only None records (s2.country = 'None')
  3. Date: Only consider year and month part, ignore days. This can be achieved by normalizing the dates of both tables to the first of the month by using date_trunc(). So, e.g. '2020-02-15' results in '2020-02-01' and '2020-02-29' results in '2020-02-01' too, which works well as comparision and join condition.

Alternatively:

SELECT
    *
FROM (
    SELECT
        sales_date,
        country,
        sales_volume,
        SUM(fix_costs) OVER (PARTITION BY date_trunc('month', sales_date)) as fix_costs
    FROM sales 
) s
WHERE country <> 'None'

You can use the SUM() window function over the group of date_trunc() as described above. Then you need filter the None records afterwards

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

If I understand correctly, use window functions:

select s.*,
       sum(fix_costs) over (partition by date_trunc(sales_date)) as month_fixed_costs
from sales;

Note that this assumes that fixed costs are NULL or 0 on other days -- which is true for the data in the question.

Upvotes: 0

Related Questions