Sum multiple counts from multiple tables

Question

I have different tables (per region) with some columns that are the same. Now I need to have a count for each value that is placed in one column over multiple tables. I'm trying to get a sum for this so I don't have to use 4 separate queries and outputs. Furthermore the values are matched with a lookup table

Region table(s) - Table1:

    id       | Column1 | 
    ---------|----------|
    1        | 1
    2        | 2
    3        | 3
etc

Lookup table

    id       | Description | 
    ---------|-------------|
    1        | Description1
    2        | Description2
    3        | Description3

The query I'm using to get the count from 1 of the tables is :

SELECT Description, Count(*) as Number from Table1, LookupTable 
WHERE Column1 = LookupTable.id GROUP BY Column1 ORDER BY Number Desc

The output is

 Description   | Number
---------------|--------
Description1   | Number
Description2   | Number
Etc.

Any idea on how to sum up the counts for each Description/value of Column1 from 4 tables that generates the output as displayed above (but then with the sum value for each description)?

valex · Accepted Answer · 2013-10-23 12:08:43Z

2

It's not clear but I guess you can use:

select LookupTable.id,LookupTable.Description, SUM(Cnt) as Number 
from LookupTable

JOIN
(
    SELECT Column1 as CId, count(*) as Cnt from Table1 group by Column1
    union all
    SELECT Column2 as CId, count(*) as Cnt from Table2 group by Column2
    union all
    SELECT Column3 as CId, count(*) as Cnt from Table3 group by Column3
    union all
    SELECT Column4 as CId, count(*) as Cnt from Table4 group by Column4
) T1 on LookupTable.id =T1.Cid
 GROUP BY LookupTable.id,LookupTable.Description
 ORDER BY Number Desc

answered Oct 23, 2013 at 12:08

valex

24.2k7 gold badges47 silver badges62 bronze badges

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

1 Comment

Chrisvdberge Over a year ago

Perfect that worked! Now what if I would want to include another column from all the tables in this? So not a count per value of Column1, but a count per combination of the value of Column1 and column2?

Code Lღver · Accepted Answer · 2013-10-23 12:05:57Z

0

Use this query:

SELECT LookupTable.Description, Count(*) as Number 
   FROM Table1, LookupTable 
      WHERE Table1.Column1 = LookupTable.id 
          GROUP BY Table1.Column1;

You have leave the name of table or its alias to call the column.

answered Oct 23, 2013 at 12:05

Code Lღver

15.6k16 gold badges61 silver badges75 bronze badges

Collectives™ on Stack Overflow

Sum multiple counts from multiple tables

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related