help with query (group by) - 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 2005 Forums

Transact-SQL (2005)

help with query (group by)

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-08-30 : 17:38:20

Hi,

I have the following table below, and I want to bring back a query that shows how many logs per day. I'm getting confused on how to group by on the date, any advice is much appreciated.

Thanks very much!!
mike123

The resultset would look something like this

logDate/Count/Description
08/08/07 / 40000 / sent
08/08/07 / 10 / failed

08/07/07 / 30000 / sent
08/07/07 / 51 / failed

CREATE TABLE [dbo].[tblMatchSendLog](
[LogID] [int] IDENTITY(1,1) NOT NULL,
[LogDate] [datetime] NOT NULL,
[UserID] [int] NOT NULL,
[Description] [nvarchar](250) NULL
) ON [PRIMARY]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-30 : 17:49:59

SELECT LogDate, COUNT(*), Description
FROM tblMatchSendLog
GROUP BY LogDate, Description
ORDER BY LogDate, 2 DESC

E 12°55'05.25"
N 56°04'39.16"

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-08-30 : 18:11:35

Hey Peso,

Sorry I didnt explain that completely right, my problem is that I am getting 100,000's of dates because its grouping each date by the second. I would like it just grouped by the actual DAY.

much appreciated, once again :)

thx,
mike123

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-08-30 : 18:15:03

[code]

SELECT
dateadd(dd,datediff(dd,0,LogDate),0) as LogDate,
COUNT(*),
Description
FROM
tblMatchSendLog
GROUP BY
dateadd(dd,datediff(dd,0,LogDate),0),
Description
ORDER BY
dateadd(dd,datediff(dd,0,LogDate),0),
COUNT(*) desc

[/code]

CODO ERGO SUM

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-08-30 : 18:28:13

awesome thanks so much!! :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources