How to sum occurences across multiple tables in Postgres

Question

I have, say, 2 tables

UK_Order and UK_Order_Item
F_Order and F_Order_Item

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.)?

Could you include CREATE TABLE statements? That'd be much better then describing them. — vyegorov
– vyegorov, Commented Oct 27, 2014 at 18:21

Community · Accepted Answer · 2020-06-20 09:12:55Z

4

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.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Oct 27, 2014 at 18:24

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

err1 Over a year ago

Thank you Erwin. Unfortunately, I'm stuck with the tables as is. As for CaMel, no worries, it's only for the example but yes, point taken. I'm no SQL expert but your answer looks so straight forward (amost wants to make me bang my head against a wall and say out loud, DUH). :D

Erwin Brandstetter Over a year ago

@user3801108: My query was missing a table alias. Also added the simple alternative, which may be faster.

err1 Over a year ago

Erwin, perfect answer. Can't vote up for some reason - 15 reputation? Yup, I'm new to Stack... Many thanks again for your quick response. Sai.

Erwin Brandstetter Over a year ago

@Sai-LeungLee: You can't upvote as new user, yet (see here), but you can always accept an answer if it actually answers your question.

Collectives™ on Stack Overflow

How to sum occurences across multiple tables in Postgres

1 Answer 1

DB design

Simpler query

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

DB design

Simpler query

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related