Select and count help - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Select and count help

Author

Topic

shwelch
Starting Member

33 Posts

Posted - 2006-09-22 : 00:46:43

I am new here, but really need some help. I have a table with something similar to this format:

id, name, value, date

there is some redunancy going on, for example, date might look like this:

001, John D, 53, 6/1/2006
002, John D, 51, 6/1/2006
003, Bill S, 33, 6/4/2006
004, John D, 84, 6/1/2006

and so on....

What I am wanting to do is select all records Where on a certain date 5 or more exists. For example, on any day that John D. has 5 or more records, I want all of those records. If on 6/4/2006 he has 4 records, I don't want those returned. I have been experimenting with count and no luck. Can someone please give me some pointers or advice on how I could do this?

I would greatly appreciate it!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-22 : 00:50:55

[code]
select *
from table t
inner join
(
select name
from table
group by name
having count(*) >= 5
) d
on t.name = d.name
[/code]

shwelch
Starting Member

33 Posts

Posted - 2006-09-22 : 09:47:56

I will have to try this when I get back at work at my pc.So you are using alias t and d to reference the same table, just two different selects? I hope this work, thank you very much!!

shwelch
Starting Member

33 Posts

Posted - 2006-09-22 : 09:59:48

Remember though, one of the criteria is on each day. There would need to be 5 or more records with the same date for each person... Where would that be added?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-22 : 11:45:46

quote:
Originally posted by shwelch

Remember though, one of the criteria is on each day. There would need to be 5 or more records with the same date for each person... Where would that be added?

quote:


select *
from   table t
inner join 
(
   select name
   from   table
   group by name
   having count(*) >= 5
) d
on t.name = d.name

shwelch
Starting Member

33 Posts

Posted - 2006-09-22 : 11:58:45

I saw the count, but where do you do anything with the date? For example, John D. could have 6/14/06 in there 8 times, but he could also have another record in there for 6/12/06 which would not need to be selected.

Wouldn't what you have return any 5 records for the person and not necessarily from the same date?

For example, if John D. had 6/12/06, 6/13/06, 6/14/06, 6/18/06, and 6/19/06, there would be a count of 5, but not 5 from one single date. Am I making sense or am I not understanding how the group you have in the sql is working?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-23 : 01:57:39

Little Modification to the Tan's Query


select *
from   table t
inner join 
(
   select name
   from   table
   group by name, [Date] 
   having count(*) >= 5
) d
on t.name = d.name

Chirag

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-23 : 05:01:35

Thanks Chiraq

shwelch
Starting Member

33 Posts

Posted - 2006-09-23 : 09:51:31

Thanks to both of you, really helpful!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources