SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Select statement - how to tally rows per hour?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mitin
Yak Posting Veteran

81 Posts

Posted - 11/26/2012 :  13:25:29  Show Profile  Reply with Quote
Hi, I have a query that extracts 24 hours worth of data from a database (see below).

What I would like to now, kind of involves two stages.

1. I would like to return only a tally of the rows of data for each hour.

2. Further, I would ultimately like to have one singular row returned for each "eventID" (as you can see this is a row in the table being queried) with a tally of how many times that "eventID" occurs for each hour.

Can somebody tell me how this is achieved?


select
firstoccurrence 
,lastoccurrence 
,servername 
,node 
,hostfunction 
,manager 
,group 
,eventid 
,summary 
,alertkey 
,key 
,tally 
from reporter_status with (nolock)
where 
lastoccurrence >= '2012-11-26'
and
lastoccurrence < '2012-11-27'


Many thanks guys :)

mitin
Yak Posting Veteran

81 Posts

Posted - 11/26/2012 :  13:28:24  Show Profile  Reply with Quote
In addition, I guess two columns would need to be created for the output, one to hold "Hour" (eg data "hr1") and one to hold "Tally" or something similar to this...

thanks again
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/26/2012 :  15:08:10  Show Profile  Reply with Quote
Do you mean something like this?
SELECT eventid,
       DATEPART(hour, lastoccurrence)  AS HourOfDay,
       COUNT(*)                        AS NumberOfEvents
FROM   reporter_status
WHERE  lastoccurrence >= '2012-11-26'
       AND lastoccurrence < '2012-11-27'
GROUP BY
       eventid,
       DATEPART(hour, lastoccurrence)
If your where clause can span more than one day,you should add date as another column in the select clause and group by clause like this:

SELECT eventid,
       DATEPART(hour, lastoccurrence)  AS HourOfDay,
       DATEADD(dd,DATEDIFF(dd,0,lastoccurrence),0) AS Date,
       COUNT(*)                        AS NumberOfEvents
FROM   reporter_status
WHERE  lastoccurrence >= '2012-11-26'
       AND lastoccurrence < '2012-11-27'
GROUP BY
       eventid,
       DATEPART(hour, lastoccurrence),
       DATEADD(dd,DATEDIFF(dd,0,lastoccurrence),0)
Go to Top of Page

mitin
Yak Posting Veteran

81 Posts

Posted - 11/28/2012 :  09:02:36  Show Profile  Reply with Quote
thanks sunitabeck :) that was absolutely perfect, really appreciate it.

I am now trying to generate a TOTAL for the number of events, so this would be a sum of the "numberofevents" column, but I don't seem to be able to do this as the column is an alias, how would I incorporate this into the query to generate the column, giving the total no. of all events for the day?

Thanks

Edited by - mitin on 11/28/2012 09:03:24
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/28/2012 :  09:17:16  Show Profile  Reply with Quote
Would one of these give you the results you are looking for?

--- 1
SELECT Date,SUM(NumberOfEvents) as DayTotal
FROM
(
	SELECT eventid,
		   DATEPART(hour, lastoccurrence)  AS HourOfDay,
		   DATEADD(dd,DATEDIFF(dd,0,lastoccurrence),0) AS Date,
		   COUNT(*)                        AS NumberOfEvents
	FROM   reporter_status
	WHERE  lastoccurrence >= '2012-11-26'
		   AND lastoccurrence < '2012-11-27'
	GROUP BY
		   eventid,
		   DATEPART(hour, lastoccurrence),
		   DATEADD(dd,DATEDIFF(dd,0,lastoccurrence),0)
) s
GROUP BY 
   Date;

--- 2

SELECT eventid,
       DATEPART(hour, lastoccurrence)  AS HourOfDay,
       DATEADD(dd,DATEDIFF(dd,0,lastoccurrence),0) AS Date,
       COUNT(*)                        AS NumberOfEvents,
       SUM(COUNT(*)) OVER(PARTITION BY DATEADD(dd,DATEDIFF(dd,0,lastoccurrence),0)) as DayTotal
FROM   reporter_status
WHERE  lastoccurrence >= '2012-11-26'
       AND lastoccurrence < '2012-11-27'
GROUP BY
       eventid,
       DATEPART(hour, lastoccurrence),
       DATEADD(dd,DATEDIFF(dd,0,lastoccurrence),0)

Edited by - sunitabeck on 11/28/2012 09:47:16
Go to Top of Page

mitin
Yak Posting Veteran

81 Posts

Posted - 11/30/2012 :  07:09:41  Show Profile  Reply with Quote
Hi, and thanks again :)

the above does work, however, i tried to break down the data further; within each date range there are different "sites" that produce "events" which add up to the total for the day. i want to get a total for each site but the statement above just seems to calculate the total for the first site and then copy that total for the rest, so the output is that all the different sites have the same event total, which is of course incorrect. not sure why its doing this....

"site" is just another column in the reporter status table by the way....

Edited by - mitin on 11/30/2012 07:11:10
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/30/2012 :  08:53:42  Show Profile  Reply with Quote
You perhaps need to add site in the select list of the inner and outer queries and in the group by clause. Which of the two queries did you use? Can you post your modified query?
Go to Top of Page

mitin
Yak Posting Veteran

81 Posts

Posted - 12/03/2012 :  16:25:54  Show Profile  Reply with Quote
Hi, when I said "site" earlier I actually should have said "servername" so just take it that I meant that.

I am using the select statement to insert the results into another table called "hourlylist", following your suggestions and using the code below:


insert into hourlylist (servername, eventid, hourofday, date, numberofevents, daytotal)

SELECT distinct servername, eventid,
       DATEPART(hour, lastoccurrence)  AS HourOfDay,
       DATEADD(dd,DATEDIFF(dd,0,lastoccurrence),0) AS Date,
       COUNT(*)                        AS NumberOfEvents,
       SUM(COUNT(*)) OVER(PARTITION BY DATEADD(dd,DATEDIFF(dd,0,lastoccurrence),0)) as DayTotal
FROM   live_data
WHERE  
lastoccurrence >= '2012-11-06'
AND lastoccurrence < '2012-11-07'
GROUP BY
       servername,
       eventid,
       DATEPART(hour, lastoccurrence),
       DATEADD(dd,DATEDIFF(dd,0,lastoccurrence),0)


however as I said the "daytotal" is the same for each "Servername", the code seems to just replicate the first total is calculates; (when I select * from hourlylist it is the same figue for the total for every row) but really I would like a total for each "servername".

If I run the above query with


WHERE servername = 'servername'
and lastoccurrence >= '2012-11-06'
AND lastoccurrence < '2012-11-07'

for each "servername" then I get totals for each.

I know its probably something not too complicated here, can anyone see why this is happeing?

Help appreciated as always!



Go to Top of Page

mitin
Yak Posting Veteran

81 Posts

Posted - 12/05/2012 :  08:20:07  Show Profile  Reply with Quote
Does anyone know what the issue is with the code?

thanks
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 12/05/2012 :  08:30:20  Show Profile  Reply with Quote
You would need to ad servername to your partition, but that may break other parts of your query

SUM(COUNT(*)) OVER(PARTITION BY ServerName, DATEADD(dd,DATEDIFF(dd,0,lastoccurrence),0))

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

mitin
Yak Posting Veteran

81 Posts

Posted - 12/14/2012 :  09:56:54  Show Profile  Reply with Quote
thanks!

I have now modified the query so that it takes into account a 'tally' column that is also in the table and calculates a sum of the tally's for each 'eventid'. I also don't need to calculate the 'hourlytotal' at the moment so have commented the lines out relating to this for the minute.

However, I also wish to still have the day total calculated for each
'servername', and this isn't working now...

The code I now have looks like this:



SELECT distinct servername, eventid,
       --DATEPART(hour, lastoccurrence)  AS HourOfDay,
       DATEADD(dd,DATEDIFF(dd,0,lastoccurrence),0) AS Date,
       SUM(tally)                        AS NumberOfEvents,
       SUM(COUNT(*)) OVER(PARTITION BY DATEADD(dd,DATEDIFF(dd,0,lastoccurrence),0)) as DayTotal
FROM   live_data
WHERE  
lastoccurrence >= '2012-11-06'
AND lastoccurrence < '2012-11-07'
GROUP BY
       servername,
       eventid,
       DATEPART(hour, lastoccurrence),
       DATEADD(dd,DATEDIFF(dd,0,lastoccurrence),0)



The issue seems to be with the line:

SUM(COUNT(*)) OVER(PARTITION BY servername, DATEADD(dd,DATEDIFF(dd,0,lastoccurrence),0)) as DayTotal


How can I retrieve the tally info and also the daytotal for each servername?

Any help grately appreciated as always!

Thank you

Edited by - mitin on 12/16/2012 05:37:59
Go to Top of Page

mitin
Yak Posting Veteran

81 Posts

Posted - 12/16/2012 :  05:43:36  Show Profile  Reply with Quote
sorry but can anyone help?

I effectively need a SUM of the 'tally' column, but since that column is being created during this query, I'm not sure how that would be done

thanks
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/16/2012 :  07:34:31  Show Profile  Reply with Quote
If you don't need it hourly, i.e., you need only daily totals, then remove the grouping by "DATEPART(hour, lastoccurrence)" from the GROUP BY clause. So one of these perhaps:
SELECT distinct servername, eventid,
       --DATEPART(hour, lastoccurrence)  AS HourOfDay,
       DATEADD(dd,DATEDIFF(dd,0,lastoccurrence),0) AS Date,
       SUM(tally)                        AS NumberOfEvents,
       SUM(COUNT(*)) OVER(PARTITION BY DATEADD(dd,DATEDIFF(dd,0,lastoccurrence),0)) as DayTotal,
       SUM(COUNT(*)) OVER(PARTITION BY servername,DATEADD(dd,DATEDIFF(dd,0,lastoccurrence),0)) as DayTotalByServer
FROM   live_data
WHERE  
lastoccurrence >= '2012-11-06'
AND lastoccurrence < '2012-11-07'
GROUP BY
       servername,
       eventid,
       --DATEPART(hour, lastoccurrence),
       DATEADD(dd,DATEDIFF(dd,0,lastoccurrence),0)
If it is none of that, can you post some sample data along with the required output?
Go to Top of Page

mitin
Yak Posting Veteran

81 Posts

Posted - 12/17/2012 :  05:11:46  Show Profile  Reply with Quote
OK thought you might be interested to know that I get this error with the above query:

Server: Msg 130, Level 15, State 1, Line 8
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/17/2012 :  08:04:23  Show Profile  Reply with Quote
I am not able to figure out what might be wrong just by inspecting the code - of course I am missing something, but just don't know what it is. Are you running the code I posted on 12/16/2012 : 07:34:31 exactly as it is? If not can you post the code you are using? Also, can you copy and run the following test code to see if that works for you?
CREATE TABLE #tmp (id INT, val int);
INSERT INTO #tmp VALUES (1,10),(1,20),(1,30),(2,10),(2,20);

SELECT
	id,
	COUNT(*) CountPerId,
	SUM(COUNT(*)) OVER () AS TotalCount
FROM
	#tmp
GROUP BY
	id;
	
DROP TABLE #tmp;
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000