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.
>

Accessing Queries in sqlDataAdapter

Hello Dears;

I have an SqlDataAdapter which contains may queries. One query contians 3 query parameters as follows:

SELECT SUM(Amount) AS Total
FROM Boxes
WHERE EntryDate BETWEEN @.date1 AND @.date2 ANDArea=@.Area

the query cannot be ran unless the queries parameters were provided. I have to access this query in code to add the parameters from controls. Is it possible to access it or use sqlDataSource instead?

Thanks alot

Here is one example I grabed from tutorial with a little modifications for your reference:

Sub GetAuthors_Click(SenderAs Object, EAs EventArgs)

Dim DSAs DataSet
Dim MyConnectionAs SqlConnection
Dim MyCommandAs SqlDataAdapter

Dim strSelectCommandAsString = "select * from Authors where state = @.State"

MyConnection =New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("PubString").ConnectionString
MyCommand =New SqlDataAdapter(strSelectCommand, MyConnection)

MyCommand.SelectCommand.Parameters.Add(New SqlParameter("@.State", SqlDbType.NVarChar, 2))
MyCommand.SelectCommand.Parameters("@.State").Value = someValue 'you need send in

DS =new DataSet()
MyCommand.Fill(DS, "Authors")

Mygv.DataSource=DS.Tables("Authors").DefaultView
Mygv.DataBind()
End Sub

You can add all your parameters to your SqlDataAdapter from your code.

|||Thanks Limno