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)
 Aggregate Function in SQL Statement.

Author  Topic 

sross81
Posting Yak Master

228 Posts
Posted - 2008-05-06 : 13:59:06
Hello,

I want to count how many occurences their is of each date that is returned by my sql query. I am not sure how to add the aggregate function code to my query I know how to just tell it to count all records, but not to tell it to count for each group of dates. For example I want it to count how many times 5/6/08 shows up in the returned results and so on. Here is my query I currently have. Any help would be greatly appreciated! Thanks!

The enc_timestamp is my date field.

Select a.template_id, a.enc_timestamp, a.created_by, b.first_name, b.last_name, b.last_name +', ' + b.first_name as fullname
From template_audit a
Join user_mstr b on a.created_by = b.user_id
GROUP BY a.template_id, a.enc_timestamp, a.created_by,b.first_name, b.last_name
Having a.template_id IN (543,3172,3031,3030,3134,3135,3171,1401,1937,3985,3173,2320,57,849,1775,1400,1747,3695,3957,3750,3954,3027,3241)
ORDER BY a.enc_timestamp, b.first_name, b.last_name;

Thanks in advance,
Sherri

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2008-05-06 : 14:03:32
Perhaps this:

SELECT enc_timestamp, COUNT(*) AS DateCount
FROM
(
	Select a.template_id, a.enc_timestamp, a.created_by, b.first_name, b.last_name, b.last_name +', ' + b.first_name as fullname
	From template_audit a 
	Join user_mstr b on a.created_by = b.user_id
	GROUP BY a.template_id, a.enc_timestamp, a.created_by,b.first_name, b.last_name
	Having a.template_id IN (543,3172,3031,3030,3134,3135,3171,1401,1937,3985,3173,2320,57,849,1775,1400,1747,3695,3957,3750,3954,3027,3241)
) dt
GROUP BY enc_timestamp


Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx
Go to Top of Page

ayamas
Aged Yak Warrior

552 Posts
Posted - 2008-05-06 : 14:29:05
Or maybe you can try the new row_number() function of SQL 2005.

Select * from
(
Select count(a.enc_timestamp),a.template_id, a.created_by, b.first_name, b.last_name, b.last_name +', ' + b.first_name as fullname,
row_number() over (partition by a.enc_timestamp order by count(a.enc_timestamp))as rowid
From template_audit a
Join user_mstr b on a.created_by = b.user_id
GROUP BY a.template_id,a.created_by,b.first_name, b.last_name
Having a.template_id IN (543,3172,3031,3030,3134,3135,3171,1401,1937,3985,3173,2320,57,849,1775,1400,1747,3695,3957,3750,3954,3027,3241)
ORDER BY a.enc_timestamp, b.first_name, b.last_name
)t
Go to Top of Page

sross81
Posting Yak Master

228 Posts
Posted - 2008-05-06 : 14:55:27
This one I quoted below works well. Although I don't have access to sql managment studio just crystal reports so I am not sure if that is what is causing my problem or not but the rows returned that are available for my report are only enc_timestamp and DateCount. I also need to be able to have created_by or user_id. The report will allow the user to select a date range and a user id. Everytime I put both in my code I end up with the date repeating itself over and over for each person instead of just summarizing it if there are more than one entries on that particular day. Can anyone help me around that problem?? Thanks so much in advance!!

quote:
Originally posted by tkizer

Perhaps this:

SELECT enc_timestamp, COUNT(*) AS DateCount
FROM
(
	Select a.template_id, a.enc_timestamp, a.created_by, b.first_name, b.last_name, b.last_name +', ' + b.first_name as fullname
	From template_audit a 
	Join user_mstr b on a.created_by = b.user_id
	GROUP BY a.template_id, a.enc_timestamp, a.created_by,b.first_name, b.last_name
	Having a.template_id IN (543,3172,3031,3030,3134,3135,3171,1401,1937,3985,3173,2320,57,849,1775,1400,1747,3695,3957,3750,3954,3027,3241)
) dt
GROUP BY enc_timestamp


Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2008-05-06 : 15:00:08
Post sample data to illustrate your problem as my solution solves what you described in your original post.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx
Go to Top of Page

sross81
Posting Yak Master

228 Posts
Posted - 2008-05-06 : 16:00:56
Well what I was saying is that I used your solution exactly as you wrote by copying and pasting:

SELECT enc_timestamp, COUNT(*) AS DateCount
FROM
(
Select a.template_id, a.enc_timestamp, a.created_by, b.first_name, b.last_name, b.last_name +', ' + b.first_name as fullname
From template_audit a
Join user_mstr b on a.created_by = b.user_id
GROUP BY a.template_id, a.enc_timestamp, a.created_by,b.first_name, b.last_name
Having a.template_id IN (543,3172,3031,3030,3134,3135,3171,1401,1937,3985,3173,2320,57,849,1775,1400,1747,3695,3957,3750,3954,3027,3241)
) dt
GROUP BY enc_timestamp

When I look at my list of fields available to drop on my report it only gives me enc_timestamp and datecount which are available in the first select statement instead of what is available in the subquery. I need to add created_by to the select statement so that it displays the id of the person that created the template so I tried this:

SELECT enc_timestamp, COUNT(*) AS DateCount, created_by
FROM
(
Select a.template_id, a.enc_timestamp, a.created_by, b.first_name, b.last_name, b.last_name +', ' + b.first_name as fullname
From template_audit a
Join user_mstr b on a.created_by = b.user_id
GROUP BY a.template_id, a.enc_timestamp, a.created_by,b.first_name, b.last_name
Having a.template_id IN (543,3172,3031,3030,3134,3135,3171,1401,1937,3985,3173,2320,57,849,1775,1400,1747,3695,3957,3750,3954,3027,3241)
) dt
GROUP BY enc_timestamp,created_by

But now for each date it doesn't count it as a group if there are more than one entries on the same date it for each user the Date Count is 1 even if its the same date and user id. I need it to subtotal by user and give a total count on each date by counting the enc_timestamp. I am sorry I wasn't specific before is that clearer?

Sherri



quote:
Originally posted by tkizer

Post sample data to illustrate your problem as my solution solves what you described in your original post.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2008-05-06 : 16:06:16
Where's the sample data?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx
Go to Top of Page

sross81
Posting Yak Master

228 Posts
Posted - 2008-05-06 : 16:12:46

I don't see where I can add an attachment. Here is a sample of what I am referring to:


created_by enc_timestamp Date Count DateCount

40 04/03/2007 2

20 05/04/2007 2

138 06/01/2007 2

46 06/14/2007 1

46 06/15/2007 1

330 06/15/2007 1

312 06/18/2007 1

138 06/18/2007 2

312 06/18/2007 1

138 06/19/2007 1

312 06/19/2007 1

330 06/20/2007 1

142 07/16/2007 1

295 07/26/2007 2

295 07/26/2007 2

46 07/26/2007 1

312 07/30/2007 2

312 07/30/2007 2

312 08/01/2007 2

312 08/01/2007 1

312 08/01/2007 1

312 08/01/2007 1

312 08/01/2007 1

312 08/01/2007 1

312 08/02/2007 1

312 08/02/2007 2

312 08/02/2007 1

312 08/02/2007 1

312 08/03/2007 1


I don't understand what it is counting when it says 2 or 1 because sometimes its still the same date and same user id. I want it to give me a total by day for each userid.

quote:
Originally posted by tkizer

Where's the sample data?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2008-05-06 : 16:14:33
I realize that's what it is returning. In your original post, you said "I want it to count how many times 5/6/08 shows up in the returned results and so on". And that's what my solution does. So if you don't want that, then please show us sample data of what you want. We can't see your system and your posts are bit confusing, so the best way to illustrate to us what you want is with sample data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx
Go to Top of Page

sross81
Posting Yak Master

228 Posts
Posted - 2008-05-06 : 16:14:59
Wow that doesn't display at all as I saw it before I posted it. I am sorry.

I think the problem is that a user can post multiple types of templates on a given day. Maybe its breaking down the count by template. Could that be possible?


quote:
Originally posted by sross81


I don't see where I can add an attachment. Here is a sample of what I am referring to:


created_by enc_timestamp Date Count DateCount

40 04/03/2007 2

20 05/04/2007 2

138 06/01/2007 2

46 06/14/2007 1

46 06/15/2007 1

330 06/15/2007 1

312 06/18/2007 1

138 06/18/2007 2

312 06/18/2007 1

138 06/19/2007 1

312 06/19/2007 1

330 06/20/2007 1

142 07/16/2007 1

295 07/26/2007 2

295 07/26/2007 2

46 07/26/2007 1

312 07/30/2007 2

312 07/30/2007 2

312 08/01/2007 2

312 08/01/2007 1

312 08/01/2007 1

312 08/01/2007 1

312 08/01/2007 1

312 08/01/2007 1

312 08/02/2007 1

312 08/02/2007 2

312 08/02/2007 1

312 08/02/2007 1

312 08/03/2007 1


I don't understand what it is counting when it says 2 or 1 because sometimes its still the same date and same user id. I want it to give me a total by day for each userid.

quote:
Originally posted by tkizer

Where's the sample data?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx
Go to Top of Page

sross81
Posting Yak Master

228 Posts
Posted - 2008-05-06 : 16:17:11
I am sorry I didn't realize I was being confusing. This is what I want.

User id 312
8/1/07 = 10
8/2/07 = 15
8/3/07 = 35
8/4/07 = 60
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2008-05-06 : 16:21:41
But you haven't shown us what your existing query displays.

You probably just need to add the Userid column to the outer GROUP BY. It's hard to know with the information that you have provided so far.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx
Go to Top of Page

sross81
Posting Yak Master

228 Posts
Posted - 2008-05-06 : 16:31:14
The big long list of user_ids, dates, and totals was what my query displayed just a piece of it because its more than 8000 rows. I have the user_id in the group_by clause.

Here is my current query:

SELECT created_by,enc_timestamp, COUNT(*) AS DateCount
FROM
(
Select a.created_by,a.enc_timestamp,b.first_name, b.last_name, b.last_name +', ' + b.first_name as fullname
From template_audit a
Join user_mstr b on a.created_by = b.user_id
GROUP BY a.template_id, a.created_by,a.enc_timestamp,b.first_name, b.last_name
Having a.template_id IN (543,3172,3031,3030,3134,3135,3171,1401,1937,3985,3173,2320,57,849,1775,1400,1747,3695,3957,3750,3954,3027,3241)
) dt
GROUP BY created_by, enc_timestamp


Here are my current results for example for one of my users:

Created_by Enc_timestamp DateCount
312 8/1/2007 2
312 8/1/2007 1
312 8/1/2007 1
312 8/1/2007 1
312 8/1/2007 1
312 8/1/2007 1
312 8/2/2007 1
312 8/2/2007 2
312 8/2/2007 1
312 8/2/2007 1

I am confused why on 8/1/07 it doesn't just add up all the occurences of 8/1/07 giving me a total of 6 on 8/1/07 instead it breaks it out 6 times and has a 2 or a 1 in the date count column. It does the same thing with 8/2/07.

I want it to look like this:

created by enc_timestamp datecount
312 8/1/07 6
312 8/2/07 4

I apoligize for any confusion I have caused I hope this looks more straight forward. I feel like the problem lies in the fact that there can be more than one type of template completed on one day so when it says their was 2 on 8/1/07 and 1 on 8/1/07 i think its breaking it down by template. I just am not sure how to not have that problem since I need to include it in the select list so that I can filter it to only include specific templates.

Sherri

quote:
Originally posted by tkizer

But you haven't shown us what your existing query displays.

You probably just need to add the Userid column to the outer GROUP BY. It's hard to know with the information that you have provided so far.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2008-05-06 : 16:40:15
Is enc_timestamp a DATETIME with a time portion? If so, then try soething like this:
SELECT created_by,enc_timestamp, COUNT(*) AS DateCount
FROM
(
Select a.created_by,DATEADD(DAY, DATEDIFF(DAY, 0, a.enc_timestamp), 0) AS enc_timestamp,b.first_name, b.last_name, b.last_name +', ' + b.first_name as fullname
From template_audit a 
Join user_mstr b on a.created_by = b.user_id
GROUP BY a.template_id, a.created_by,a.enc_timestamp,b.first_name, b.last_name
Having a.template_id IN (543,3172,3031,3030,3134,3135,3171,1401,1937,3985,3173,2320,57,849,1775,1400,1747,3695,3957,3750,3954,3027,3241)
) dt
GROUP BY created_by, enc_timestamp
Or this maybe? 
SELECT 
	a.created_by, 
	DATEADD(DAY, DATEDIFF(DAY, 0, a.enc_timestamp), 0) AS enc_timestamp, 
	COUNT(*) AS DateCount
FROM 
	template_audit AS a 
INNER JOIN
	user_mstr b 
	on a.created_by = b.user_id
WHERE
	a.template_id IN 
	(543,3172,3031,3030,3134,3135,3171,1401,1937,3985,3173,2320,
	57,849,1775,1400,1747,3695,3957,3750,3954,3027,3241)
GROUP BY 
	a.created_by, 
	DATEADD(DAY, DATEDIFF(DAY, 0, a.enc_timestamp), 0)
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2008-05-06 : 16:41:57
The problem is most likely due to time data in the enc_timestamp column.

You should be running your queries in Management Studio before ever copying them into Crystal. You need to get them working there first. You said you don't have access to Management Studio, so get access to it! It is very important for troubleshooting purposes to take Crystal out of the picture. It would have been obvious that the time data was screwing up the query if Crystal was out of the picture.

Try this:


SELECT created_by, DATEADD(Day, DATEDIFF(Day, 0, enc_timestamp), 0) AS enc_timestamp, COUNT(*) AS DateCount
FROM
(
	Select a.created_by,a.enc_timestamp,b.first_name, b.last_name, b.last_name +', ' + b.first_name as fullname
	From template_audit a 
	Join user_mstr b on a.created_by = b.user_id
	GROUP BY a.template_id, a.created_by,a.enc_timestamp,b.first_name, b.last_name
	Having a.template_id IN (543,3172,3031,3030,3134,3135,3171,1401,1937,3985,3173,2320,57,849,1775,1400,1747,3695,3957,3750,3954,3027,3241)
) dt
GROUP BY created_by, DATEADD(Day, DATEDIFF(Day, 0, enc_timestamp), 0)


Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2008-05-06 : 16:43:14
quote:
Originally posted by Lamprey

Is enc_timestamp a DATETIME with a time portion? If so, then try soething like this:
SELECT created_by,enc_timestamp, COUNT(*) AS DateCount
FROM
(
Select a.created_by,DATEADD(DAY, DATEDIFF(DAY, 0, a.enc_timestamp), 0) AS enc_timestamp,b.first_name, b.last_name, b.last_name +', ' + b.first_name as fullname
From template_audit a 
Join user_mstr b on a.created_by = b.user_id
GROUP BY a.template_id, a.created_by,a.enc_timestamp,b.first_name, b.last_name
Having a.template_id IN (543,3172,3031,3030,3134,3135,3171,1401,1937,3985,3173,2320,57,849,1775,1400,1747,3695,3957,3750,3954,3027,3241)
) dt
GROUP BY created_by, enc_timestamp




It needs to be changed in the inner GROUP BY as well, otherwise an error will occur.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2008-05-06 : 16:45:10
quote:
Originally posted by tkizer
[br
It needs to be changed in the inner GROUP BY as well, otherwise an error will occur.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx
AHh yes, that is true. cood catch.
Go to Top of Page

sross81
Posting Yak Master

228 Posts
Posted - 2008-05-06 : 16:48:26
Thank you for catching that about the timestamp. Due to my inexperience with sql I did not catch that. I beg for management studio all the time but for some odd reason they just won't install it on my machine. I realize that it would make my job easier. I am sorry for the trouble but am so thankful for both of your help on this issue!! :)
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site