How to GROUP BY and aggregate fields after JOINS in Query

chris 0
Viewed 50

I have the following data which I got from the following query:

date quantity name season_id contract_id signing_date
1 2016-07-01 00:00:00 3 John Doe 4 3000 2016-10-20
2 2021-07-28 00:00:00 14 John Doe 5 3541 2021-01-28
3 2016-08-15 00:00:00 10 John Doe 5 3000 2016-10-20
4 2016-08-02 00:00:00 5 John Doe 5 1528 2016-03-02 
WITH ws AS (select date, quantity,
name, season_id, contract_id, contract.signing_date
FROM warehouse_state
JOIN inventory ON inventory.id = warehouse_state.inventory_id
JOIN owner ON owner.inventory_id = warehouse_state.id
JOIN season ON season.id = owner.season_id
JOIN contract ON contract.id = warehouse_contract.contract_id
GROUP BY date, quantity, name, season.id, contract.id, signing_date)

Now, I am having trouble aggregating the ws records based on dates.

Let's say I want a SUM of quantity grouped by date where date is date before contract signing_date. Not sure how to proceed with this, and probably it can be done in a single query without having a WITH x AS query or something actually using it like:

SELECT * FROM ws
LEFT JOIN contract on contract.contract_id = ws.contract_id
-- Here set following condition: for any ws record that has `date` before `signing_date`, SUM quantity and return aggregate

Expected output:

contract_id signing_date quantity name
3000 2016-10-20 18 John Doe
3541 2021-01-28 18 John Doe
1528 2021-01-28 0 John Doe

In the expect output quantity is a SUM, and the record is grouped by contract. In the first record, #1, #3, and #4 were aggregated because their date values are before the contract (3000) signing_date. Even though, the 4th record does not have the same contract_id, it's also aggregated because its date field is before the signing date in contract 3000. Similarly, when grouped by contract 3541, record #2 is excluded from the aggregation because its date value is not before the signing_date of contract 3541.

Any suggestions? Thanks

aggregate-functions group-by join sql postgresql
1 Answers

Does that SQL really compile? Reason is I see you referencing an inventory table that I don't see anywhere.

Also you are grouping on all columns -- essential a "select distinct." Is that what you meant to do?

That aside, assuming your joins are correct and a couple of other assumptions, I'm going to sub them all with "< your tables and joins >." I think all you want is a simple aggregate. No need for a CTE (with clause).

select
  date, sum (quantity)
FROM
  < your tables and joins >
where
  date < signing_date
GROUP BY
  date

Alternatively, you can see the total quantity for all dates AND the total quantity before the contract date using a filter:

select
  date, sum (quantity) as total_quantity,
  sum (quantity) filter (where date < signing_date) as qty_before_contract_sign
FROM
  < your tables and joins >
GROUP BY
  date

If you wanted to see the other columns as well, then you want a windowing function. Let me know if that's the case and I can demonstrate.

-- EDIT 9/7/22 --

Based on your update, I think this is what you want:

select
  contract_id, contract.signing_date, sum (quantity) as quantity,
  name
FROM warehouse_state
JOIN inventory ON inventory.id = warehouse_state.inventory_id
JOIN owner ON owner.inventory_id = warehouse_state.id
JOIN season ON season.id = owner.season_id
JOIN contract ON contract.id = warehouse_contract.contract_id
where
  date < contact.signing_date
GROUP BY
  contract_id, contract.signing_date, name

But the one gotcha is Contract 1528 will not show up in this output since it's filtered out by the where condition.

I'm not fond of this, but you could keep the filter to overcome this... maybe there's a better solution.

select
  contract_id, contract.signing_date,
  coalesce (sum (quantity) filter (where date < contact.signing_date), 0) as quantity,
  name
FROM warehouse_state
JOIN inventory ON inventory.id = warehouse_state.inventory_id
JOIN owner ON owner.inventory_id = warehouse_state.id
JOIN season ON season.id = owner.season_id
JOIN contract ON contract.id = warehouse_contract.contract_id
GROUP BY
  contract_id, contract.signing_date, name

Also, my output does not match yours, but I'm hoping that's because of sample data.

Hambone0
Related