Simple aggrigate query 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

General SQL Server Forums

New to SQL Server Programming

Simple aggrigate query help

Author

Topic

lynda
Starting Member

21 Posts

Posted - 2009-08-06 : 12:51:42

Howdy folks. I am trying to produce a simple output that would look like this:

Date of access | Count of hits on that date

My query is this:

Use lynda

select cast(datepart(month, accessdate) as nvarchar(2)) + '-' + cast(datepart(day, accessdate) as nvarchar(2)) + '-' + cast(datepart(year, accessdate) as nvarchar(4)) as [Access Date], count(accessdate) as [Access Count]
from lyndalog.dbo.accessedproducts
left join products on products.externalid=accessedproducts.productexternalid
left join users on users.externalid=accessedproducts.userexternalid
where userexternalid in
(select externalid
from users
where users.id in
(select gi.userid
from groupinvitations gi
left join groups on groups.id=gi.groupid
left join users on users.id=groups.owneruserid
where users.email='bentr001'))
group by accessdate

What I get back is like this:

Date of access | Count of hits on that date
05-01-2009 | 1
05-01-2009 | 1
05-01-2009 | 1
05-01-2009 | 1
05-01-2009 | 1
05-01-2009 | 1

Where what I need is this:

Date of access | Count of hits on that date
05-01-2009 | 6

Any help appreciated!

Thanks!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-08-06 : 13:24:25

Use your cast in group by too and it will work.

No, you're never too old to Yak'n'Roll if you're too young to die.