Accumelated Sum

Hi,
I have a query:
SELECT DISTINCT [Col015], [Col014], SUM([Col098]) AS [AggrigatedCol098]
FROM [MyTable]
WHERE ISNULL(CAST([Col015] AS VARCHAR), '') IN (
SELECT [UniqueCol]
FROM (
SELECT DISTINCT ISNULL(CAST([Col015] AS VARCHAR), '') AS [UniqueCol],
[Col015], [Col014]
FROM [Drek]
WHERE [Col015] = 'Sigma Lite-500' OR [Col015] = 'Mark 4s Rack' GROUP BY
[Col015], [Col014]
) AS ['Temp']
)
AND [Col044] = '1030833573'
GROUP BY [Col015], [Col014]
ORDER BY [AggrigatedCol098] DESC
And I would like to add a column that holds the accumulated sum of the
virtual column [AggrigatedCol098] that I created in my query. Any Ideas?
Thanks, Aviad.You can use WITH ROLLUP (after GROUP BY but before ORDER BY) to get a row
with the total for all rows. It will also give rows with totals grouped by
all columns 'rolling up', so in your case you would get a separate row with
the total for each [Col15].
Jacco Schalkwijk
SQL Server MVP
"Aviad" <Aviad@.discussions.microsoft.com> wrote in message
news:1BBCD3A9-879B-48CF-A30B-0DB6CAF6436A@.microsoft.com...
> Hi,
> I have a query:
> SELECT DISTINCT [Col015], [Col014], SUM([Col098]) AS [AggrigatedCol098]
> FROM [MyTable]
> WHERE ISNULL(CAST([Col015] AS VARCHAR), '') IN (
> SELECT [UniqueCol]
> FROM (
> SELECT DISTINCT ISNULL(CAST([Col015] AS VARCHAR), '') AS [UniqueCol],
> [Col015], [Col014]
> FROM [Drek]
> WHERE [Col015] = 'Sigma Lite-500' OR [Col015] = 'Mark 4s Rack' GROUP BY
> [Col015], [Col014]
> ) AS ['Temp']
> )
> AND [Col044] = '1030833573'
> GROUP BY [Col015], [Col014]
> ORDER BY [AggrigatedCol098] DESC
> And I would like to add a column that holds the accumulated sum of the
> virtual column [AggrigatedCol098] that I created in my query. Any Ideas?
> Thanks, Aviad.
>|||You can use this quick example as the starting point.
select orderid,freight,(select sum(freight) from orders o2 where
o2.orderid<=o1.orderid) as acc_sum
from orders o1
where orderid<10260
order by orderid
-oj
"Aviad" <Aviad@.discussions.microsoft.com> wrote in message
news:1BBCD3A9-879B-48CF-A30B-0DB6CAF6436A@.microsoft.com...
> Hi,
> I have a query:
> SELECT DISTINCT [Col015], [Col014], SUM([Col098]) AS [AggrigatedCol098]
> FROM [MyTable]
> WHERE ISNULL(CAST([Col015] AS VARCHAR), '') IN (
> SELECT [UniqueCol]
> FROM (
> SELECT DISTINCT ISNULL(CAST([Col015] AS VARCHAR), '') AS [UniqueCol],
> [Col015], [Col014]
> FROM [Drek]
> WHERE [Col015] = 'Sigma Lite-500' OR [Col015] = 'Mark 4s Rack' GROUP BY
> [Col015], [Col014]
> ) AS ['Temp']
> )
> AND [Col044] = '1030833573'
> GROUP BY [Col015], [Col014]
> ORDER BY [AggrigatedCol098] DESC
> And I would like to add a column that holds the accumulated sum of the
> virtual column [AggrigatedCol098] that I created in my query. Any Ideas?
> Thanks, Aviad.
>