I have an ‘Orders’ table and a ‘Records’ table.
Orders table has the following columns:
order_id order_date seller order_price
Records table has the following columns:
order_id record_created_at record_log
I’m trying to pull and compile the following list of data but I keep getting an error message:
order_week seller total_num_orders under100_count --this is the number of orders that were < $100 over100_count --this is the number of order that >= $100 approved --this is the number of orders that were approved by the payment platform
Here’s my query:
SELECT order_week, seller, total_num_orders, under100_count, over100_count, approved FROM ( SELECT EXTRACT(WEEK FROM order_created_at) AS order_week, merchant_name AS seller, COUNT(merchant_name) AS total_num_orders, SUM(DISTINCT total_order_price < 100) AS under100_count, SUM(DISTINCT total_order_price >= 100) AS over100_count FROM orders o GROUP BY order_week, seller) INNER JOIN ( SELECT COUNT(DISTINCT o.order_id) AS approved FROM records r WHERE record_log = 'order approved' GROUP BY order_id) ON l.order_id = o.order_id;
What am I doing wrong?
Advertisement
Answer
The subquery in the join
needs an alias. It also needs to return the order_id
column, so it can be joined.
inner join ( select order_id, ... from records ... group by order_id) r --> here on l.order_id = o.order_id
I would actually write your query as:
select extract(week from o.order_created_at) as order_week, o.merchant_name as seller, count(*) as total_num_orders, sum(o.total_order_price < 100) as under100_count, sum(o.total_order_price >= 100) as over100_count, sum(r.approved) approved from orders o inner join ( select order_id, count(*) approved from records r where record_log = 'order approved' group by order_id ) r on r.order_id = o.order_id; group by order_week, seller, approved
Rationale:
you don’t want, and need,
distinct
in the aggregate functions here; it is inefficient, and might even yield wrong resultscount(*)
is more efficientcount(<expression>)
– so, use it, unless you know why you are doing otherwiseI removed an unecessary level of nesting
If there are orders without records, you might want a left join
instead.