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)
 Getting Totals for Tally Column, Couple Issues....

Author  Topic 

mitin
Yak Posting Veteran

81 Posts

Posted - 2013-02-17 : 06:15:01
Hi, please see the query I've posted below, which selects some fields from a table based on a certain date range, and also calculates the following fields:

'NumberofEvents' - This should be the number of events for each individual eventid, if other values vary then it shouldn't affect this column, since it is adding up the tally of those rows with the same Eventid, it shouldn't matter if the other fields vary.

'DayTotalByServer' - The total number of events for all rows with the same 'servername', calculated by adding up the tally field of those rows.

'DayTotal' - The total of the tally column for all rows within the specified date range.

Here is the query:


use database

select distinct
servername,
firstoccurrence,
lastoccurrence,
eventid,
key,
group,
node,
summary,
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 databasetable
where firstoccurrence >= '2013-01-16' and firstoccurrence < '2013-01-17'
and lastoccurrence >= '2013-01-16' and lastoccurrence < '2013-01-16'

group by
firstoccurrence,
lastoccurrence,
servername,
eventid,
alertkey,
alertgroup,
node,
summary

order by numberofevents desc


The problem I have at the minute is that:

1.) The 'NumberofEvents' field is not calculated how I want it to be, it calculates a tally for each row based on whether all the fields in the select statement are the same, as opposed to just the event ID being the same. Basically I want the rows to be returned with the eventid and other fields to go with it, but although the other fields may vary from row to row, if the eventid in 5 rows are the same for example, although the values in the other selected fields may vary for those rows, those rows should be given the same 'NumberofEvents' because this should be calculated based purely upon Eventid.

2.) Also, I'm not sure whether the 'Daytotalbyserver' and 'daytotal' fields are being calculated accurately. 'Daytotal' should be a sum of the tally column based upon servername. Whilst 'daytotal' should be a sum of the tally column for all rows within the specified date range....

I really really hope I have explained myself well enough here, please help if you can, im still new to SQL and trying to keep my head above water with a DB I have inherited.

Help is greatly appreciated! thank you!





James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-17 : 08:30:23
Without some sample input data and the desired output, it is hard to tell how you should write the query. The following will calculate the sums as I understood it. It will produce one row for every row in the databasetable that satisfy the where clause. Perhaps that is not what you want? Do you want one row per eventid or one row per server, or some other type of output?
SELECT DISTINCT 
servername,
firstoccurrence,
lastoccurrence,
eventid,
[key]
[group] ,
node,
summary,
SUM(tally) OVER(PARTITION BY eventid) AS NumberofEvents,
SUM(tally) OVER( PARTITION BY DATEADD(dd, DATEDIFF(dd, 0, lastoccurrence), 0) ) AS daytotal,
SUM(tally) OVER( PARTITION BY servername,
DATEADD(dd, DATEDIFF(dd, 0, lastoccurrence), 0) ) AS daytotalbyserver
FROM
databasetable
WHERE
firstoccurrence >= '2013-01-16' AND firstoccurrence < '2013-01-17'
AND lastoccurrence >= '2013-01-16' AND lastoccurrence < '2013-01-16'
ORDER BY
numberofevents DESC
Go to Top of Page

mitin
Yak Posting Veteran

81 Posts

Posted - 2013-02-17 : 12:12:23
Thanks for the reply James, I want a total for each eventid, however the same eventid might (or definately will actually) come from different servers , and might have other values that are different(summary, key, node etc), I still want these differing rows to be displayed, however since they have the same event id, the 'numberofevents' which is the total for that eventid over all servers, should be the same...

there should then be a different total for eventid's for each servername (totalby server), and a grand total of all the event id's added togther (daytotal). This is all using the tally field

does this make sense?

thanks
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-17 : 12:27:00
Can you post some sample data and the corresponding output that you want to get? For me (and probably for many others on the forum), looking at the data is more meaningful and easier to understand than description in words. Does the query I posted earlier give you wrong results?
Go to Top of Page

mitin
Yak Posting Veteran

81 Posts

Posted - 2013-02-17 : 13:24:40
Hi James, sorry but I can't test your query right now. Will be able to tomorrow though :)

Here is some sample data with the results I want, I hope it makes things a bit clearer

servername	  firstoccurrence	  lastoccurrence     eventid	key	group	node	summary	         tally         
abiobjcl 16/01/2013 21:28 16/01/2013 21:29 41 key1 group1 node1 critical alert 1
abiobjcl 16/01/2013 22:28 16/01/2013 23:28 41 key1 group2 node1 critical alert 1
abiobjcl 16/01/2013 23:28 16/01/2013 21:28 41 key2 group1 node1 critical alert 1
lchobjcl 16/01/2013 21:28 16/01/2013 21:28 45 key5 group2 node4 caution alert 1
lchobjcl 16/01/2013 21:28 16/01/2013 21:29 45 key5 group2 node4 caution alert 1

servername firstoccurrence lastoccurrence eventid key group node summary noofevents daytotalbyserver daytotal
abiobjcl 16/01/2013 21:28 16/01/2013 21:29 41 key1 group1 node1 critical alert 3 3 5
abiobjcl 16/01/2013 22:28 16/01/2013 23:28 41 key1 group2 node1 critical alert 3 3 5
abiobjcl 16/01/2013 21:28 16/01/2013 21:28 41 key2 group1 node1 critical alert 3 3 5
lchobjcl 16/01/2013 21:28 16/01/2013 21:28 45 key5 group2 node4 caution alert 2 2 5

I have also just realised that I don't actually want the firstoccurence and lastoccurrence fields to be selected, since this will return too may rows, as these values will always be unique to each record.
Go to Top of Page

mitin
Yak Posting Veteran

81 Posts

Posted - 2013-02-17 : 13:46:04
sorry, just reformatted the example data above, hopefully thats clearer :)
Go to Top of Page

mitin
Yak Posting Veteran

81 Posts

Posted - 2013-02-17 : 16:09:43
make sense? :)
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-17 : 17:41:38
Got it. So the query I posted earlier with one change should work:
SELECT DISTINCT 
servername,
firstoccurrence,
lastoccurrence,
eventid,
[key]
[group] ,
node,
summary,
SUM(tally) OVER(PARTITION BY eventid,servername) AS NumberofEvents,
SUM(tally) OVER( PARTITION BY DATEADD(dd, DATEDIFF(dd, 0, lastoccurrence), 0) ) AS daytotal,
SUM(tally) OVER( PARTITION BY servername,
DATEADD(dd, DATEDIFF(dd, 0, lastoccurrence), 0) ) AS daytotalbyserver
FROM
databasetable
WHERE
firstoccurrence >= '2013-01-16' AND firstoccurrence < '2013-01-17'
AND lastoccurrence >= '2013-01-16' AND lastoccurrence < '2013-01-16'
ORDER BY
numberofevents DESC
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-17 : 23:21:56
quote:
Originally posted by James K

Got it. So the query I posted earlier with one change should work:
SELECT DISTINCT 
servername,
firstoccurrence,
lastoccurrence,

eventid,
[key]
[group] ,
node,
summary,
SUM(tally) OVER(PARTITION BY eventid,servername) AS NumberofEvents,
SUM(tally) OVER( PARTITION BY DATEADD(dd, DATEDIFF(dd, 0, lastoccurrence), 0) ) AS daytotal,
SUM(tally) OVER( PARTITION BY servername,
DATEADD(dd, DATEDIFF(dd, 0, lastoccurrence), 0) ) AS daytotalbyserver
FROM
databasetable
WHERE
firstoccurrence >= '2013-01-16' AND firstoccurrence < '2013-01-17'
AND lastoccurrence >= '2013-01-16' AND lastoccurrence < '2013-01-17'
ORDER BY
numberofevents DESC



couple of small typos

Also
@OP

why is last row of sample data missing from the output? is it intentional?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

mitin
Yak Posting Veteran

81 Posts

Posted - 2013-02-18 : 04:07:02
Hi, yes its intentional, because those last two rows in the example data are the same, apart from the firstoccurrence/lastoccurrence fields, which I don't actually want included in my select anymore....
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-18 : 04:13:22
Ok..then James's suggestion with my last modification should work for you

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -