CODEWITHSUNDEEP
sqlpostgresqldatabase-designcountaggregate-functions
err1
err1

Reputation: 499

How to sum occurences across multiple tables in Postgres

I have, say, 2 tables

UK_Order (and F_Order) has the fields: Id, orderStatus. UK_Order_Item (and F_Order_Item) has the fields: orderId, model, and order_quantity.

I'm trying to write the SQL to return the total number of different models sold through out the UK and F but can't. So far I've come up with:

SELECT model, SUM(order_quantity) AS quantityOrdered 
FROM uk_order_item 
WHERE orderId 
IN
(
    SELECT Id FROM uk_order WHERE orderStatus = 'Incomplete'
)
GROUP BY model

and also

SELECT model, SUM(order_quantity) AS quantitySold
FROM f_order_item 
WHERE orderId 
IN
(
    SELECT Id FROM f_order WHERE orderStatus = 'Incomplete'
)
GROUP BY model

returning eg:

Model, QuantityOrdered
Volkswagen, 3
Ford, 2
Citroen, 4
...

and then to find the total number of volkwagens sold in the UK and F, I have to do a little bit calcluation (ie volkswagenCount = uk's volkwagen's quantitySold + F's volkwagen's quantitySold

In other words, my separate queries return the total models sold for the UK, and F(rance) - I would repeat this query for Germany, Spain etc. But is there a way of returning the total number for each model sold throughout Europe (UK, France, Germany etc.)?

Upvotes: 3

Views: 3220

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658072

DB design

I would rethink your db design. Unified tables including all countries would probably be a better idea. So you would have single tables for all countries:

order_item 
order

Just add a country_id to table order.

Simpler query

The IN construct is needlessly complex. Simpler and faster:

SELECT oi.model, sum(oi.order_quantity) AS quantity_ordered 
FROM   uk_order      o
JOIN   uk_order_item oi ON oi.orderid = o.id
WHERE  o.orderstatus = 'Incomplete'
GROUP  BY 1;

While you have separate tables, nothing will be much simpler or faster than adding up separate counts:

SELECT model, sum(quantity_ordered) AS total_quantity_ordered
FROM  (
   SELECT model, sum(order_quantity) AS quantity_ordered
   FROM   uk_order      o
   JOIN   uk_order_item oi ON oi.orderid = o.id
   WHERE  o.orderstatus = 'Incomplete'
   GROUP  BY 1

   UNION  ALL
   SELECT model, sum(order_quantity) AS quantity_ordered
   FROM   f_order       o
   JOIN   f_order_item  oi ON oi.orderid = o.id
   WHERE  o.orderstatus = 'Incomplete'
   GROUP  BY 1
   ) sub
GROUP  BY 1;

You could just merge rows it into a single derived table with UNION ALL before summing:

SELECT model, sum(quantity_ordered) AS total_quantity_ordered
FROM  (
   SELECT model, order_quantity AS quantity_ordered
   FROM   uk_order      o
   JOIN   uk_order_item oi ON oi.orderid = o.id
   WHERE  o.orderstatus = 'Incomplete'

   UNION  ALL
   SELECT model, order_quantity AS quantity_ordered
   FROM   f_order       o
   JOIN   f_order_item  oi ON oi.orderid = o.id
   WHERE  o.orderstatus = 'Incomplete'
   ) sub
GROUP  BY 1;

But I doubt it will be faster.

And my standing advice is not to use CaMeL case identifiers in Postgres, quoted or not. Makes your life easier.

Upvotes: 4

Related Questions