Counts on multiple tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Counts on multiple tables

Author

Topic

kwalt
Starting Member

4 Posts

Posted - 2009-02-17 : 14:15:04

I have multiple tables in access. All the tables have the same columns. Im looking to get a count on how many businesses are under each sic code description. For example, how many doctors, lawyers, dentists there are in all my tables. I broke the tables down by counties.

My SQL Statement looks like this:

SELECT sic_code_description, COUNT(sic) AS "Count SIC"
FROM hc_no_indv_docs_lawyers
GROUP BY sic_code_description;

this works for one table but how do i add the others?

I'd appreciate some help

Thanks

mfemenel
Professor Frink

1421 Posts

Posted - 2009-02-17 : 14:40:42

You could use a union statement if you're looking to join them back as 1 result set.

select * from mytable
UNION
select * from myothertable
UNION
select * from mythirdtable.

Mike
"oh, that monkey is going to pay"

kwalt
Starting Member

4 Posts

Posted - 2009-02-17 : 14:43:30

I was looking to get something like this:

Sic Code Description Sic Count
Doctors 18
Lawyers 10
Dentists 14

Im trying to find out how many records are under each Sic code description

kwalt
Starting Member

4 Posts

Posted - 2009-02-17 : 14:46:26

i tried union select and i got something like this:

sic code description Count Sic
doctors 10
doctors 14
lawyers 7
lawyers 2
dentists 12
dentists 14

mfemenel
Professor Frink

1421 Posts

Posted - 2009-02-17 : 14:54:07

Sorry, I didn't realize you'd have the sam sic in each table. In that case, try wrapping that union statement like this
select sic,sum(CountSic)
from(
select sic,count(*)as CountSic from table1
Union
select sic, count(*) as CountSic from table2
)Group By SIC

Mike
"oh, that monkey is going to pay"

kwalt
Starting Member

4 Posts

Posted - 2009-02-17 : 16:39:51

i tried this

select sic_code_description, sum(sic_code_description)
from(
select sic_code_description, count(sic_code_description) as "Number of Records" from hc_no_indv_docs_lawyers
Union
select sic_code_description, count(sic_code_description) as "Number of Records" from pc_no_indv_docs_lawyers
)Group By sic_code_description;

and access told me "you tried to execute a query that does not include the specified expression sic_code_description as part of an aggregate.

im new at this SQL stuff

mfemenel
Professor Frink

1421 Posts

Posted - 2009-02-17 : 16:48:11

In your select statement outside the unions you are trying to sum sic_code_description. You would need to sum "Number of Records"

select sic_code_description, sum(Number of Records)

Mike
"oh, that monkey is going to pay"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources