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)
 Getting Totals for Tally Column, Couple Issues....
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mitin
Yak Posting Veteran

81 Posts

Posted - 02/17/2013 :  06:15:01  Show Profile  Reply with Quote
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!






Edited by - mitin on 02/17/2013 06:19:42

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 02/17/2013 :  08:30:23  Show Profile  Reply with Quote
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 - 02/17/2013 :  12:12:23  Show Profile  Reply with Quote
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

Edited by - mitin on 02/17/2013 12:16:35
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 02/17/2013 :  12:27:00  Show Profile  Reply with Quote
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 - 02/17/2013 :  13:24:40  Show Profile  Reply with Quote
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.

Edited by - mitin on 02/17/2013 13:47:55
Go to Top of Page

mitin
Yak Posting Veteran

81 Posts

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

mitin
Yak Posting Veteran

81 Posts

Posted - 02/17/2013 :  16:09:43  Show Profile  Reply with Quote
make sense? :)
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 02/17/2013 :  17:41:38  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 02/17/2013 :  23:21:56  Show Profile  Reply with Quote
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/


Edited by - visakh16 on 02/17/2013 23:22:29
Go to Top of Page

mitin
Yak Posting Veteran

81 Posts

Posted - 02/18/2013 :  04:07:02  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 02/18/2013 :  04:13:22  Show Profile  Reply with Quote
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
  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.08 seconds. Powered By: Snitz Forums 2000