ihateartists ihateartists -4 years ago 38
SQL Question

SQL join/sum assistance, need to combine the 2 queries inside

Here's a fiddle:
http://sqlfiddle.com/#!2/7f479a/2

What I am trying to achieve is the Inventory from this query:

SELECT
di.distributors_inventory_stock,
op.op_products_id,
p.products_mfr_part_number,
op.op_products_name,
p.products_brand,
op.op_products_qty,
SUM(op.op_products_qty) AS TotalSold
FROM orders_products op
JOIN orders o
ON op.op_order_id = o.orders_id
JOIN products p
ON p.products_id = op.op_products_id
JOIN distributors_inventory di
ON di.distributors_inventory_product_ID = op.op_products_id
WHERE o.orders_distributor_id = '90'
AND o.orders_date_purchased BETWEEN '2014-06-01 00:00:00' AND '2014-08-01 23:59:59'
GROUP BY op.op_products_id
ORDER BY TotalSold DESC


With the TotalSold and product list from this query:

SELECT
op.op_products_id,
p.products_mfr_part_number,
op.op_products_name,
p.products_brand,
SUM(op.op_products_qty) AS TotalSold
FROM orders_products op
JOIN orders o ON op.op_order_id = o.orders_id
JOIN products p ON p.products_id = op.op_products_id
WHERE o.orders_distributor_id = '90'
AND o.orders_date_purchased BETWEEN '2014-06-01 00:00:00' AND '2014-08-01 23:59:59'
GROUP BY op.op_products_id
ORDER BY TotalSold DESC

Answer Source

Because the distributors data is driving the list of products, that needs to be the first table queried, then left join to the other tables, putting all conditions on the other tables in the join condition:

SELECT
  di.distributors_inventory_stock stock,
  di.distributors_inventory_product_ID product_id,
  p.products_mfr_part_number mfr_part_number,
  op.op_products_name order_product_name,
  p.products_brand brand,
  p.products_supplier_name supplier_name,
  SUM(op.op_products_qty) / count(o.orders_id) AS TotalSold
FROM distributors_inventory di
LEFT JOIN orders_products op ON di.distributors_inventory_product_ID = op.op_products_id
LEFT JOIN orders o ON op.op_order_id= o.orders_id
  AND o.orders_date_purchased BETWEEN '2014-06-01 00:00:00' AND '2014-08-01 23:59:59'
LEFT JOIN products p ON p.products_id = di.distributors_inventory_product_ID
WHERE di.distributors_inventory_distributor_ID = '90'
GROUP BY 1, 2, 3, 4, 5, 6
ORDER BY TotalSold DESC

See SQLFiddle

The key points here are:

  • select first from the table that controls what rows are returned
  • left join to the sales tables
  • move the conditions on left joined tables, specifically the date range, from the where clause into the join condition
  • removed column that was both summed and selected (makes no sense)
  • corrected group by clause problem (you must list all non-aggregated selected columns for the grouping to work as you expect)
  • join to product table directly from distributors table so product info is available for products without sales in the period
  • give columns friendlier names
  • added supplier product name (because we can)
  • modified sum() due to account for multiple joined rows from the order table (from comment)

Of all the tips, the third is the most critical. Because left joined rows have nulls in their columns, putting a condition on them in the where clause will result in only rows that actually join being returned - effectively making the joins inner joins. Conditions in the join condition still restrict rows returned, while allowing the join to be an outer join

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download