Select statement - how to tally rows per hour? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2008 Forums

Transact-SQL (2008)

Select statement - how to tally rows per hour?

Author

Topic

mitin
Yak Posting Veteran

81 Posts

Posted - 2012-11-26 : 13:25:29

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 - 2012-11-26 : 13:28:24

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-26 : 15:08:10

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)

mitin
Yak Posting Veteran

81 Posts

Posted - 2012-11-28 : 09:02:36

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-28 : 09:17:16

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)

mitin
Yak Posting Veteran

81 Posts

Posted - 2012-11-30 : 07:09:41

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....

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-30 : 08:53:42

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?

mitin
Yak Posting Veteran

81 Posts

Posted - 2012-12-03 : 16:25:54

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

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

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!

mitin
Yak Posting Veteran

81 Posts

Posted - 2012-12-05 : 08:20:07

Does anyone know what the issue is with the code?

thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-12-05 : 08:30:20

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

mitin
Yak Posting Veteran

81 Posts

Posted - 2012-12-14 : 09:56:54

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

mitin
Yak Posting Veteran

81 Posts

Posted - 2012-12-16 : 05:43:36

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-16 : 07:34:31

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?

mitin
Yak Posting Veteran

81 Posts

Posted - 2012-12-17 : 05:11:46

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.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-17 : 08:04:23

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;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources