Sum of Top 7 rows - then next 7 rows....

[vnad]

I am working with a table that I want to take the first seven rows, group and sum them, write them to a table then take the next seven rows, group and sum them, write them to a table on so on. Do you know of any good ways of doing this? Thanks,

Dan

 

[JayKusch]

Are you returning a set of records from a query or are you just taking the top 7 records from a table and then the next 7 ... , with some type of unique key?

Thanks

J. Kusch

 

[vnad]

I am taking to the top 7 from a table however we will not have a unique key for this table unless we put in a autonumber.

dan

 

[sunila7]

Can u give a set of sample data for us to look at?

Thanks

Sunil

 

[vnad]

Sure, here is the table we are looking at

User Query Start Time
Dan select * Act 2003-11-10 10:47:23.000
Dan select * Ath 2003-11-10 10:47:23.600
Dan select * cdh 2003-11-10 10:47:24.000

End Time Duration
2003-11-10 10:47:23.500 500
2003-11-10 10:47:24.000 400
2003-11-10 10:47:24.800 800

and so on. This is the database results from a SQL profiler and we are trying to group certain commands together so we can get the sum of the group of commands to get an overall time. Are are trying to group all 7 commands together.

 

[sunila7]

Does this query give what u r looking for? I have made an assumption here that the start time is distinct.

SELECT max([user]),sum(duration) from
(SELECT TOP 100 percent *, (SELECT count(*) FROM TBlname t1 where t1. = t.[user ]and t1.starttime<=t.starttime )/8 gpcol from TBLName t order by starttime ) TBL
GROUP by gpcol

Sunil

 

[vnad]

That would be good if we want to get the average of all transactions that user did. We want to get a result set something like this.

User Start of First Trans Sum of Duration
Dan 2003-11-10 10:47:23.000 1700
Dan 2003-11-10 12:57:28.120 1800
Kelly 2003-11-10 12:58:43.050 700
Dan 2003-11-10 13:55:23.100 2000

The records would be the sum of the 7 transactions for that particular instance. So we want to group the 7 consecutive commands that happen after the user clicks a certain button (getNext) on the application. We want to know how long it took our database to respond to this command (sum of the 7 transactions)

 

[sunila7]

Can you explain a little more about how you can identify the set of transactions for that particular instance from the data in that table?


Sunil

 

[vnad]

Every getNext has a set of seven commands it runs. So we can find out when a user hits getNext by looking at the commands in profiler. If we see the seven transactions, one after another, then we can determine the user hit getNext.