By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,617 Members | 1,792 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,617 IT Pros & Developers. It's quick & easy.

Using count() across multiple tables

P: 1
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.
Mar 21 '07 #1
Share this Question
Share on Google+
1 Reply


iburyak
Expert 100+
P: 1,017
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]
Mar 21 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.