Creating SQL values from two columns using the selective aggregate of each column

Question

I have the following four tables: region_reference, community_grants, HealthWorkers and currency_exchange

and the follow SQL query which works:

SELECT HealthWorkers.worker_id
  , community_grants.percentage_price_adjustment
  , community_grants.payment_status
  , community_grants.chosen
  , (region_reference.base_price * currency_exchange.euro_value) AS price
FROM currency_exchange 
  INNER JOIN (
    region_reference INNER JOIN (
      HealthWorkers INNER JOIN community_grants 
        ON HealthWorkers.worker_id = community_grants.worker_id
    ) ON (
      region_reference.community_id = community_grants.community_id
    ) AND (region_reference.region_id = community_grants.region_id)
  ) 
ON currency_exchange.currency = HealthWorkers.preferred_currency
WHERE (
  HealthWorkers.worker_id="malawi_01" 
    AND community_grants.chosen=True
);

It gives me the following result set:

However, my task is to create an entity that includes just 4 values.

type OverallPriceSummary struct {
    Worker_id          string         `json:"worker_id"`
    Total_paid        decimal.Decimal `json:"total_paid"`
    Total_pledged     decimal.Decimal `json:"total_pledged"`
    Total_outstanding decimal.Decimal `json:"total_outstanding"`
}

Total_paid is the sum of values for the specified worker_id where payment_status = “1” (combined for all records)

Total_outstanding is the sum of values where payment_status is “0” and chosen is true (combined for all records)

Total_pledged is the sum of Total_paid and Total_outstanding (also combined for all records)

I currently obtain these values by aggregating this manually in my code as postgresql iterates through the resultset but I believe there is a way to avoid this interim SQL query and get what I need from a single SQL query. I suspect it involves the use of SUM AS and inner queries but I don’t know how to bring it all together. Any help or direction would be much appreciated.

EDIT: I have provided some sample data below:

region_reference

region_id	region_name	base_price	community_id
1	Lilongwe	100	19
2	Mzuzu	50	19

HealthWorkers

worker_id	worker_name	preferred_currency	billing_address	charity_logo
malawi_01	Raphael Salanga	EUR	Nkhunga Health Centre in Nkhotakota District	12345

community_grants

region_id	campaign_id	worker_id	percentage_price_adjustment	community_id	payment_status	chosen	paid_price
1	1	malawi_01	10	19	0	Yes	0
2	1	malawi_01	0	19	1	Yes	20
3	1	malawi_01	1	19	0	Yes	0
1	1	malawi_01	0	23	0	Yes	30

currency_exchange

currency	currency_symbol	euro_value
EUR	€	1
USD	$	0.84

Please provide some sample data, in your question, and the result you want to achieve with that data — NickW
– NickW, Commented Sep 4, 2021 at 12:54
Your image and SQL indicates MS Access but you mention and tag Postgres. Are you using Postgres linked tables in Access GUI? Or do you need a pass-through query? This is important because SQL dialects will differ between Access and Postgres. — Parfait
– Parfait, Commented Sep 4, 2021 at 14:59
@Parfait I inherited an MSAccess database so I use it to throw Queries together and the Queries also works in Postgresql. I happy for the dialect to be purely Postgresql specific though. I guess it would also be useful know how to optimize what MS Access generated though. — Paul Ryan
– Paul Ryan, Commented Sep 4, 2021 at 15:05
And what is sum of values? The sum of price expressed as: region_reference.base_price * currency_exchange.euro_value) AS price? — Parfait
– Parfait, Commented Sep 4, 2021 at 15:17
its slightly more convoluted, (region_reference.base_price * currency_exchange.euro_value)+(currency_exchange.euro_value*region_reference.base_price * (community_pricing.percentage_price_adjustment/100)) AS price, but same idea applies — Paul Ryan
– Paul Ryan, Commented Sep 4, 2021 at 15:39

Parfait · Accepted Answer · 2021-09-04 17:15:41Z

1

Consider conditional aggregation using Postgres' FILTER clause where you pivot data to calculated conditional columns.

Below assumes sum of values is the sum of calculated price expressed as: region_reference.base_price * currency_exchange.euro_value. Adjust as needed.

SELECT h.worker_id
   , SUM(r.base_price * ce.euro_value) FILTER(WHERE
        cg.payment_status = 1
     ) AS total_paid
   , SUM(r.base_price * ce.euro_value) FILTER(WHERE
        cg.payment_status = 0 AND 
        cg.chosen=True
     )  AS total_outstanding
   , SUM(r.base_price * ce.euro_value) FILTER(WHERE
        (cg.payment_status = 1) OR 
        (cg.payment_status = 0 AND cg.chosen=True)
     )  AS total_pledged

FROM  community_grants cg
INNER JOIN region_reference r
  ON  r.community_id = cg.community_id
  AND r.region_id = cg.region_id
INNER JOIN HealthWorkers h
  ON  h.worker_id = cg.worker_id
  AND h.worker_id = 'malawi_01'
INNER JOIN currency_exchange ce
  ON ce.currency = h.preferred_currency

GROUP BY h.worker_id

edited Sep 4, 2021 at 17:15

answered Sep 4, 2021 at 15:23

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Paul Ryan Over a year ago

I needed to add FILTER( where to each use of Filter but it works well

Parfait Over a year ago

Whoops! Thanks for that overlook. Edited accordingly. Alternatively, use CASE (portable to other RDBMS) but unfortunately not Access yet!

NickW · Accepted Answer · 2021-09-04 15:18:44Z

1

Try something like:

SELECT
worker_id
,sum(case when payment_status = “1” 
      then paid_price else 0 end) as Total_paid
,sum(case when payment_status = “0” and chosen = true
      then paid_price else 0 end) as Total_outstanding
,sum(case when (payment_status = “1”) 
     or (payment_status = “0” and chosen = true)
      then paid_price else 0 end) as Total_pledged
from community_grants
group by worker_id

answered Sep 4, 2021 at 15:18

NickW

10k2 gold badges10 silver badges26 bronze badges

1 Comment

Paul Ryan Over a year ago

Just realised this skips the link to the other tables

Collectives™ on Stack Overflow

Creating SQL values from two columns using the selective aggregate of each column

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related