Select statement - 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 statement

Author

Topic

dturner
Starting Member

24 Posts

Posted - 2005-12-14 : 01:34:05

Hello all and I am happy to have joined the forum.

Got a question

I am trying to select records from a table which
have seen a VW Bug >= 5 times in one day

My table hold information such as
Dateseen (DateTeim)
Email (Char)
Name (Char)
ID (Number)

What is the best way to select this information?

So table has such data as

2001-05-05 15:23:27.453 ME@yahoo.com Joe 4112
2000-12-22 08:11:12.857 notME@yahoo.com Mikey 235
2000-05-05 16:21:37.497 ME@yahoo.com Joe 4112
2000-10-02 16:21:05.030 notyou@yahoo.com Johnson 22222
1998-12-18 08:46:06.900 she@yahoo.com Elliot 1
2001-05-05 12:41:37.587 ME@yahoo.com Joe 4112
2001-02-11 20:36:08.520 him@yahoo.com Tim 34255
2001-05-05 05:29:16.800 ME@yahoo.com Joe 4112
2001-10-05 10:58:07.310 earth@eartlink.net sue 23232
2001-05-05 18:59:12.427 ME@yahoo.com Joe 4112

This is what I would want returned:

Name email Count
Joe ME@yahoo.com 5

The world has more information to offer than I can hold in my head

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-14 : 01:38:39

Select Name, Email, count(*) as [Count] from yourTable
group by Name, Email having count(*)>=5

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-14 : 01:43:46

you can use Group By .. Having to do this.

select [Name], email, count(*)
from yourtable
group by [Name], email
having count(*) >= 5

-----------------
[KH]
Learn something new everyday

dturner
Starting Member

24 Posts

Posted - 2005-12-14 : 01:46:15

Ok, I think that would work if Me@yahoo.com only saw VW Bugs for one day, but what if Me@yahoo.com saw 2 yesterday and 3 last year on a certin day.

And I threw in a
Declare @startdate Datetime --as the start of look up
Declare @endDate Datetime --as the end of the look

Set @startdate '12/25/2003'
Set @enddate'12/25/2005'

The world has more information to offer than I can hold in my head

dturner
Starting Member

24 Posts

Posted - 2005-12-14 : 01:48:21

Thank you all

Remember I want to select only those records that have
seen a VW BUG 5 or more times a day
in a time frame of say 3 years

The world has more information to offer than I can hold in my head

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-14 : 01:50:01

select [Name], email, count(*)
from yourtable
where Dateseen >= @startdate
and Dateseen <= @enddate
group by [Name], email
having count(*) >= 5

-----------------
[KH]
Learn something new everyday

dturner
Starting Member

24 Posts

Posted - 2005-12-14 : 01:52:32

Ah yes that would return joe Me@yahoo.com 10

which would be during that time frame and not for each day

The world has more information to offer than I can hold in my head

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-14 : 01:58:45

[code]select Name, email, cnt
from (
select Name, convert(char(8), DateSeen, 112) as dtseen, email, count(*) as cnt
from yourtable
where Dateseen >= @startdate
and Dateseen <= @enddate
group by Name, convert(char(8), DateSeen, 112), email
having count(*) > 5
) as t[/code]

-----------------
[KH]
Learn something new everyday

dturner
Starting Member

24 Posts

Posted - 2005-12-14 : 02:00:17

khtan

Thank you, I'll have to look at that again in the morning.

The world has more information to offer than I can hold in my head

dturner
Starting Member

24 Posts

Posted - 2005-12-14 : 02:24:06

Khtan...
Thank you very much! That is what I needed.

Also a big warm thank you for all those who helped.

The world has more information to offer than I can hold in my head

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources