Monday, March 12, 2012

Problem doing a count query using 2 tables

I've got one table that holds various information fields and a field for the
city, and another table that holds city and state (and zip, etc). I want to
query both tables to show the number of states for which records in the first
table were submitted. Problem is that although I can query a count of
records by city in table 1, or I can combine the fields from both tables, I
can't seem to manage to do both in 1 query. All I want is to select a date
range, reference the city field in one table to "city" in the other, and show
a count of the states. It's got to be simple but I can't figure it out. Can
anyone help with this? Thanks!
chazz
If i understand your requirement correctly, following example may help you.
Please post relevent table structure/sample records/expected result set to
understand your problem correctly.
ex:
create table emp_master(name varchar(25),city varchar(25))
create table city_master(city varchar(25),state varchar(25), zip
varchar(25))
go
insert into emp_master values('name1','city1')
insert into emp_master values('name2','city1')
insert into emp_master values('name3','city2')
insert into emp_master values('name4','city2')
insert into emp_master values('name5','city2')
insert into emp_master values('name7','city4')
insert into emp_master values('name8','city5')
insert into emp_master values('name9','city5')
insert into city_master values('city1', 'state1', 'xxxx')
insert into city_master values('city2', 'state1', 'xxxx')
insert into city_master values('city3', 'state1', 'xxxx')
insert into city_master values('city4', 'state2', 'xxxx')
insert into city_master values('city5', 'state3', 'xxxx')
go
--query to get the number of states for which records in the first
table(emp_master) were submitted
select b.state, count(b.state) as 'no_of_counts'
from emp_master a join city_master b
on a.city = b.city
group by b.state
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com

No comments:

Post a Comment