SQL Count from different tables in one Query

Question

I assume this was asked already once but I didn't find the right answer unfortunately.

I have multiple tables around customer data and all have the foreign key company_id. My goal is to have a list in which the first column gives me the company_id and the other columns aggregated values (e.g. COUNT) from other table. All grouped by the company_id

For example:

company_id | COUNT(products) | COUNT(purchases)

My approach was something like this but it didn't work out and I can't make up my mind of it (apart from using vlookups in excel)

SELECT pro.company_id, COUNT(*)
(SELECT pu.company_id, COUNT(*) FROM purchases pu GROUP BY pu.company_id) as 'COUNT(Purchases)'
FROM products pro
GROUP BY pro.company_id

Skip the GROUP BY in the subqeury, instead put a WHERE clause condition refrencing the pro table. — jarlh
– jarlh, Commented Mar 26, 2021 at 14:54
@GordonLinoff Thank you! This was easier than expected... Unfortunately, I didn't understand the union_all approach you suggested me 😅 Hence, I'm happy that this works fine as well — Valentin Roessler
– Valentin Roessler, Commented Apr 6, 2021 at 17:53

Gordon Linoff · Accepted Answer · 2021-03-26 14:55:18Z

1

I would suggest using union all and aggregation:

SELECT p.company_id, SUM(is_product), SUM(is_purchase)
FROM ((SELECT pr.company_id, 1 as is_product, 0 as is_purchase
       FROM products pro
      ) UNION ALL
      (SELECT pu.company_id, 0 as is_product, 1 as is_purchase
       FROM purchases pu
      )  pp      
GROUP BY p.company_id

Note that this keeps all products in either table, so the results can have 0 counts in one or the other columns.

answered Mar 26, 2021 at 14:55

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

Comments

dnoeth · Accepted Answer · 2021-03-26 16:07:38Z

0

Aggregate each table and then join to customer:

SELECT com.company_id, "COUNT(Purchases)", "COUNT(Products)"
from companies as com
left join
 (
   SELECT company_id, COUNT(*) as "COUNT(Purchases)"
   FROM purchases 
   GROUP BY company_id
 ) as pu
on com.company_id = pu.company_id
left join
 (
   SELECT company_id, COUNT(*) as "COUNT(Products)"
   FROM products 
   GROUP BY company_id
 ) as pro

If there's no row for a customer this displays NULL, if you want to return 0 switch to coalesce("COUNT(Products)", 0)

answered Mar 26, 2021 at 16:07

dnoeth

60.5k4 gold badges45 silver badges63 bronze badges

3 Comments

Valentin Roessler Over a year ago

Thank you! This works well for me :) I was wondering what would you do differently if you'd query aggregated values from the companies table and add the COUNT(Purchases) and COUNT(Products) to it?

dnoeth Over a year ago

I don't understand exactly what you try to do, but you can easily add a GROUP BY to the outer select and sum("COUNT(Purchases)")

Valentin Roessler Over a year ago

Okay, let me try this again. It didn't work when I tried to add the GROUP BY to the other select statement. That's why I asked

Collectives™ on Stack Overflow

SQL Count from different tables in one Query

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related