0

I know the title is a bit mysterious. I just would like to ask how I can optimize this query to work better

I have orders and order_items. Every order_items has order_id from "orders" table so that same order_id can be in various order_items since in and order someone can purchase more than one order_item

I reached my goal with this query. But I think it could have been written better, more optimized.

My goal is to find distinct number of order_ids and total amount of all order_items associated with these order_ids

SELECT
  COUNT(*)       AS total_orders,
  SUM(total_invoice_amount) AS total_amount
FROM
  (
    SELECT
      SUM(invoice_amount) AS total_invoice_amount
    FROM
      order_items a
    GROUP BY
      order_id
  ) b;
  • 3
    Please tag your DBMS, you have tagged this question as mysql and postgres – McNets Apr 4 '18 at 18:36
2

My goal is to find distinct number of order_ids and total amount of all order_items associated with these order_ids

You can get same result using a single query:

SELECT
  COUNT(DISTINCT order_id) AS total_orders, 
  SUM(invoice_amount) AS total_invoice_amount
FROM
  order_items;
  • Rather than writing my goal, I could have written the query lol. Thanks :) – SNaRe Apr 4 '18 at 19:00
  • 1
    The query is equivalent only if order_id is not nullable but that's a fair assumption I think. +1 – ypercubeᵀᴹ Apr 4 '18 at 19:04
  • @ypercubeᵀᴹ:NULL values are considered equal by DISTINCT and GROUP BY, so the result is identical. But I am not sure this is faster than the original query of the OP, because count(DISTINCT ...) is probably just as expensive as the subquery in the original. – Erwin Brandstetter Apr 5 '18 at 13:32
  • 1
    @ErwinBrandstetter NULL values are indeed considered equal by DISTINCT and GROUP BY. But McNets query (if there were nulls in order_id) won't count them because COUNT( order_id) won't (with ether DISTINCT in it or not). with o (order_id) as (values (null), (null)) select count(*) from (select order_id from o group by order_id) g ;: gives 1, while with o (order_id) as (values (null), (null)) select count(distinct order_id) from o ; gives 0. – ypercubeᵀᴹ Apr 5 '18 at 13:46
  • 1
    You are right, McNets' count would be off by one, I missed that. – Erwin Brandstetter Apr 5 '18 at 16:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.