PostgreSQL Performance Optimization – Count and Sum Values

optimizationperformancepostgresqlquery-performance

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;

Best Answer

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;
Related Question