Using count() across multiple tables

[ntocher]

Is it possible in a single query to search up to 12 tables for a similar text string and count the number each time a duplicate is found?

I have a table for each month with a string field that contains a referer string.

table = stats_2007_01
table = stats_2007_02
table = stats_2007_03 and so on....
field = [referer] string (200)

I can query a single table and return the result i expect with this statement

SELECT COUNT(referer) AS refcount, referer
FROM stats_2007_03
GROUP BY referer

the results i get for this one table are
refcount referer
11 http://abc.com
21 httP://123.com
34 and so on.....

I would like to run this query against all 12 tables and show the same 3 records but with the refcount counting matches found in the other tables too..

I have tried left outer joins, union all, inner joins, but just cant seem to find the solution.

I feel it may not be possible and I may need to sort the result recordset using vbscript before outputing to screen..

If anyone can help, please let me know!...
Many thanks in advance.

I am running MS SQL 7, Active server pages, W2K3 server.

[iburyak]

Try this:


[PHP]SELECT COUNT(referer) AS refcount, referer
FROM (Select referer from stats_2007_01
UNION
Select referer from stats_2007_02
UNION
Select referer from stats_2007_03
UNION
and so on... ) a
GROUP BY referer[/PHP]