Grouping records from 2 (or more) different tables

On Fri, 7 Dec 2007 05:07:01 -0800, Raf wrote:

>Hello,
>
>I log all actions of the users in a table. Each day, I create this 'logtable'.
>It looks something like this:
>Tablename: 20071207 (for logs of dec 07, 2007)
> Recnr
> Userid
> ActionID
>
>Now, i want to create statistics on the actions that users perform.
>If I only use 1 table, there is no problem: I use the Group by statement and
>the job is done. The problems start when I want to create statistics on more
>than one table. I think I should use the union (or union all) statement, but
>I just can't get it working combined with the group by statement.

Hi Raf,

It appears that you have just found out why it is not a good idea to
create a new table for each day :)
The recommended method is to use one table, and to have the current date
as an extra column (often also part of the primary key) in that table.

If your table is extremely large (and note that a few million rows is
not extremely large for SQL Server), than you might wish to look into
table partitioning (SQL Server 2005 only - a feature that allows you to
divide a table into logical partitions that are handled seperately) or
into partitioned views (all versions - a feature that allows you to
create seperate tables and one view that combines the data).

For a quick fix until you have time to reallly fix the issues, you can
use either

SELECT ....
FROM (SELECT Userid, ActionID, CAST('20071207') AS TheDate
FROM [20071207]
UNION ALL
SELECT Userid, ActionID, CAST('20071208') AS TheDate
FROM [20071208]) AS d
GROUP BY ....;

You can also try if you get better performance by pre-aggregating before
union'ing, for example

SELECT ActionID, SUM(CountPerDay) AS TotalCount
FROM (SELECT ActionID, COUNT(*)
FROM [20071207]
GROUP BY ActionID
UNION ALL
SELECT ActionID, COUNT(*)
FROM [20071208]
GROUP BY ActionID) AS d
GROUP BY ActionID;

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis