Subquery In Postgresql not matching data expected

Question

When the query below is used I get 7 records from the database.

SELECT pickup_date::date, 
       SUM(CASE WHEN paid ='Yes' THEN price  ELSE 0  END) AS  TotalMoMoPaid 
from requests 
where order_status = 'Done' 
  and payment_mode = 'MoMo' 
  and pickup_date::date >= current_timestamp::date - INTERVAL '7 days' 
GROUP BY pickup_date::date,paid,order_status,price

When the same query is used as a sub query, I get 2 records which is not what I expect,

SELECT pickup_date::date,
       sub.TotalMoMoPaid, 
       SUM(CASE WHEN order_status ='Done' THEN price  ELSE 0  END) AS "TotalCashSales" 
from (
  SELECT paid as subPaid,
         order_status as subStatus,
         price as subPrice, 
         SUM(CASE WHEN paid ='Yes' THEN price  ELSE 0  END) AS  TotalMoMoPaid 
  from requests 
  where order_status = 'Done' 
    and payment_mode = 'MoMo' 
    and pickup_date::date >= current_timestamp::date - INTERVAL '7 days' 
  GROUP BY pickup_date::date,subPaid,subStatus,subPrice
) AS sub, requests  
where order_status ='Done' 
  and payment_mode = 'Cash' 
  and pickup_date::date >= current_timestamp::date - INTERVAL '7 days'
GROUP BY sub.TotalMoMoPaid,subPaid,pickup_date::date 
ORDER BY sub.TotalMoMoPaid,pickup_date::date

Never use commas in the FROM clause. Always use proper, explicit, standard, readable JOIN syntax. After all, what would you expect from a Cartesian product other than multiplying the number of rows? — Gordon Linoff
– Gordon Linoff, Commented Aug 24, 2020 at 10:54

Mustafa Tarım · Accepted Answer · 2020-08-24 12:26:12Z

1

This query should work instead of using subqueries;

SELECT pickup_date::date,
       SUM(CASE WHEN payment_mode = 'MoMo' and paid = 'Yes' THEN price  ELSE 0  END) AS TotalMoMoPaid,
       SUM(CASE WHEN payment_mode = 'Cash' and paid = 'Yes' THEN price  ELSE 0  END) AS TotalCashSales
FROM requests 
WHERE order_status = 'Done' 
      and pickup_date::date >= current_timestamp::date - INTERVAL '7 days' 
GROUP BY pickup_date::date,paid,order_status,price

edited Aug 24, 2020 at 12:26

answered Aug 24, 2020 at 10:59

Mustafa Tarım

966 bronze badges

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

3 Comments

lutakyn Over a year ago

Ok sure but the query is now bring out data of same dates on different rows, it has to sum up for same dates. check the link below ibb.co/qgJsMg5

Mustafa Tarım Over a year ago

I think payment mode in group by causes this, so i edited the query. Can you try the new version?

lutakyn Over a year ago

Thanks for your help, it fixed after I removed paid,order_status,price from the GROUP BY

Collectives™ on Stack Overflow

Subquery In Postgresql not matching data expected

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related