Re: Problem doing a count query using 2 tables
From: Vishal Parkar (REMOVE_THIS_vgparkar_at_yahoo.co.in)
Date: 10/18/04
- Previous message: chazz: "Problem doing a count query using 2 tables"
- In reply to: chazz: "Problem doing a count query using 2 tables"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 18 Oct 2004 11:26:22 +0530
adding date component to my earlier query can have query as shown in
following example.
ex:
create table emp_master(name varchar(25),city varchar(25), dt datetime)
create table city_master(city varchar(25),state varchar(25), zip
varchar(25))
go
insert into emp_master values('name1','city1', getdate())
insert into emp_master values('name2','city1', getdate())
insert into emp_master values('name3','city2', getdate() -1)
insert into emp_master values('name4','city2', getdate() -1)
insert into emp_master values('name5','city2', getdate() -2)
insert into emp_master values('name7','city4', getdate() -2)
insert into emp_master values('name8','city5', getdate() -3)
insert into emp_master values('name9','city5', getdate() -3)
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
where a.dt between '20041016' and '20041018 23:59:59'
group by b.state
If above does not satisfy your requirement post DDL/some sample records and
expected result-set.
-- Vishal Parkar vgparkar@yahoo.co.in | vgparkar@hotmail.com
- Previous message: chazz: "Problem doing a count query using 2 tables"
- In reply to: chazz: "Problem doing a count query using 2 tables"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
