Combining data from multiple tables when using Group By

February 9, 2022

I have data in about 20 different tables. Each table has a column EnterDateTime for the data / time the data in those tables was added. I’m already using a combination of DATEPART (ISO_WEEK) and GROUP BY to view how much data was added for each week using the following query:

SELECT DATEPART (ISO_WEEK, EnterDateTime) WeekNum, COUNT (*) as COUNT
FROM TABLE 1
GROUP BY DATEPART (ISO_WEEK, EnterDateTime)
ORDER BY WeekNum

Which is giving results like this, for the data in Table 1:

WeekNum | Count
1       | 1084
2       | 5257
3       | 2152
4       | 5172

I am then doing the same query on Table 2:

WeekNum | Count
1       | 362
2       | 153
3       | 78
4       | 216

And so on, and so on, for each of the 20 tables. This is time consuming.

Ideally, I’d like to see this in a single output, like this, so I only have to run one query, instead of about 20:

WeekNum | Table 1 Count  | Table 2 Count | Table 3 Count
1       | 1084           | 362           |  x
2       | 5257           | 153           |  x
3       | 2152           | 78            |  x
4       | 5172           | 216           |  x

Is this possible? Thank you for your help.

>Solution :

With a bit of text editing efforts you can create a query type of

select WeekNum, min(Count1) Count1,.. min(Count20) Count20
from(
    SELECT DATEPART(ISO_WEEK, EnterDateTime) WeekNum, COUNT (*) as COUNT1, null  as COUNT2, .., null as COUNT20
    FROM TABLE1
    GROUP BY DATEPART (ISO_WEEK, EnterDateTime)

    union all
    ..

    SELECT DATEPART(ISO_WEEK, EnterDateTime) WeekNum, null, .., null, COUNT(*)
    FROM TABLE20
    GROUP BY DATEPART (ISO_WEEK, EnterDateTime)
) t
group by WeekNum
order by WeekNum;