| Author |
 Topic  |
|
|
mitin
Yak Posting Veteran
54 Posts |
 Posted - 11/26/2012 : 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
54 Posts |
Posted - 11/26/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/26/2012 : 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
54 Posts |
Posted - 11/28/2012 : 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 |
Edited by - mitin on 11/28/2012 09:03:24 |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/28/2012 : 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) |
Edited by - sunitabeck on 11/28/2012 09:47:16 |
|
|
|
mitin
Yak Posting Veteran
54 Posts |
Posted - 11/30/2012 : 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.... |
Edited by - mitin on 11/30/2012 07:11:10 |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/30/2012 : 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
54 Posts |
Posted - 12/03/2012 : 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
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!
|
|
|
|
mitin
Yak Posting Veteran
54 Posts |
Posted - 12/05/2012 : 08:20:07
|
Does anyone know what the issue is with the code?
thanks |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2866 Posts |
Posted - 12/05/2012 : 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
54 Posts |
Posted - 12/14/2012 : 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 |
Edited by - mitin on 12/16/2012 05:37:59 |
|
|
|
mitin
Yak Posting Veteran
54 Posts |
Posted - 12/16/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 12/16/2012 : 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
54 Posts |
Posted - 12/17/2012 : 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
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 12/17/2012 : 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; |
|
|
| |
Topic |
|
|
|
Select statement - how to tally rows per hour?
