CODEWITHSUNDEEP
sqlpostgresql
lutakyn
lutakyn

Reputation: 474

Subquery In Postgresql not matching data expected

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

enter image description here

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

enter image description here

Upvotes: 1

Views: 78

Answers (1)

Mustafa Tarım
Mustafa Tarım

Reputation: 96

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

Upvotes: 1

Related Questions