Prev: How to create select query?
Next: Can I use LEFT JOIN to solve this problem ?
From: Vijay on 27 Jun 2008 05:08
1. How can I do running totals group wise efficiently and without using SP?

ID GROUP DATE
VALUE RUNNING TOTAL
12323434 1 1/4/07
121323.32 ?
12323434 2 1/4/07
121344.32 ?
12323434 1 1/4/07
434344.32 ?
12323434 2 1/4/07
545454.32 ?


2. In a large data table how can I sum upon a given field in the most
efficient manner or improve the performance?


ID GROUP DATE
VALUE
12323434 1 1/4/07
121323.32
12323434 2 1/4/07
121344.32
12323434 1 1/4/07
434344.32
12323434 2 1/4/07
545454.32
...
....
.... 10 million rows

Regards,
Amit





No virus found in this incoming message.
Checked by AVG.
Version: 7.5.524 / Virus Database: 270.4.0/1508 - Release Date: 18/06/2008
9:08 PM

From: Sha Anand on 27 Jun 2008 05:39
This query will give you the running total. I assumed the ID column as a
Sort Order column.


SELECT
ID,GroupNum,Date,Value, (SELECT SUM(rt.Value) FROM TableA rt WHERE
rt.GroupNum = oq.GroupNum rt.Id <= oq.Id) as RunningTotal
FROM
TableA oq

- Sha Anand

"Vijay" wrote:

> 1. How can I do running totals group wise efficiently and without using SP?
>
> ID GROUP DATE
> VALUE RUNNING TOTAL
> 12323434 1 1/4/07
> 121323.32 ?
> 12323434 2 1/4/07
> 121344.32 ?
> 12323434 1 1/4/07
> 434344.32 ?
> 12323434 2 1/4/07
> 545454.32 ?
>
>
> 2. In a large data table how can I sum upon a given field in the most
> efficient manner or improve the performance?
>
>
> ID GROUP DATE
> VALUE
> 12323434 1 1/4/07
> 121323.32
> 12323434 2 1/4/07
> 121344.32
> 12323434 1 1/4/07
> 434344.32
> 12323434 2 1/4/07
> 545454.32
> ...
> ...
> ... 10 million rows
>
> Regards,
> Amit
>
>
>
>
>
> No virus found in this incoming message.
> Checked by AVG.
> Version: 7.5.524 / Virus Database: 270.4.0/1508 - Release Date: 18/06/2008
> 9:08 PM
>
 | 
Pages: 1
Prev: How to create select query?
Next: Can I use LEFT JOIN to solve this problem ?