I have a database with 2 tables:

• suporder which contains order data (PK: Id)
  • Has a column for the orders value called orderamt
• supexpiry which holds expiry data (FK orderid references suporder.Id)
  • Has a column for the expiry value called expiryamt

I have the query below working, but I am stuck on an additional requirement I have. I need to retrieve the COUNT & SUM of supexpiry.expiryamt where supexpiry.expiryamt is greater than 2% of the relevant suporder.orderamt.

select distinct(s.merchantId::int), s.merchantName, count(s.id)::int as orderCount, sum(s.orderamt) as orderAmt, count(s2.orderid)::int as expiryCount, sum(s2.expiredamt) as expiryAmt
from suporder s 
left join supexpiry s2 on s2.orderid = s.id
where s.orderdate >= '2021-09-01'::date and s.orderdate <= '2021-10-13'::date
group by s.merchantid, s.merchantname 

So ideally the output would look like the following (with the last 2 being the new columns):

Any help is much appreciated, thank you.

CodePudding user response：

Change count(s2.orderid) to sum(case when s2.expiredamt/s.orderamt > .02 then 1 else 0 end) and sum(s2.expiredamt) to sum(case when s2.expiredamt/s.orderamt > .02 then s2.expiredamt else 0 end)

Or, if you don't like repetition, you can cross join lateral (values (case when s2.expiredamt/s.orderamt > .02 then 1 else 0 end)) expired(multiplier) and then use sum(expired.multiplier) and sum(expired.multiplier * s2.expiredamt)

CodePudding user response：

It seems the issue you are encountering is that you want to filter your data after grouping it using the "group by" clause. That is, after data has been aggregated. To do the kind of filtering you want, you'll need to use the having keyword. Having is basically the same as where, except that it works on data after it has been aggregated.

So your query will look like

select...
where...
group by...
having...