sql script - grant total

the supplied sql script works fine and produces the following.

how do I script -- adding an accumulative column (adding accumulative all the subtotals) - and then a final grand total cell.


Frequency  ChargeableAmount    SubTotals
     2          0                       0
     4      100000                  400000
     3      200000                  600000
    17      500000                 8500000
     5      1000000                 5000000
SELECT COUNT(RecordID) AS Frequency, ChargeableAmount AS ChargeableAmounts, SUM (ChargeableAmount) AS SubTotals

FROM dbo.abcData

WHERE (TransactionKey = 8)
  AND (TransactionTime BETWEEN '2011-03-01 00:00:00.000' AND '2011-03-01 23:59:59.999')
  
GROUP BY ChargeableAmount

Open in new window

amillyardAsked:
Who is Participating?
 
Pratima PharandeCommented:
0
 
Pratima PharandeCommented:
not sure what you exactly want

did you want one new column with 3 of this which is having total of all subtotals ...it is same for all...is this?


 SELECT COUNT(RecordID) AS Frequency, ChargeableAmount AS ChargeableAmounts, SUM (ChargeableAmount) AS SubTotals , (select  sum(ChargeableAmount) from dbo.abcData) as grandtotal

FROM dbo.abcData

WHERE (TransactionKey = 8)
  AND (TransactionTime BETWEEN '2011-03-01 00:00:00.000' AND '2011-03-01 23:59:59.999')
 
GROUP BY ChargeableAmount
0
 
reb73Commented:
Something like this?
;WITH CTE (RowNo, Frequency, ChargeableAmounts, SubTotals)
AS
(
	SELECT	ROW_NUMBER() OVER (order by ChargeableAmount),
		COUNT(RecordID) AS Frequency, ChargeableAmount AS ChargeableAmounts, SUM (ChargeableAmount) AS SubTotals
	FROM	dbo.abcData
	WHERE (TransactionKey = 8)
	  AND (TransactionTime BETWEEN '2011-03-01 00:00:00.000' AND '2011-03-01 23:59:59.999')
	GROUP BY ChargeableAmount 
)
SELECT RowNo, Frequency, ChargeableAmounts, SubTotals, (SELECT SUM(b.SubTotals) FROM CTE b WHERE b.RowNo <= a.RowNo) as RunningTotals
FROM CTE a
COMPUTE SUM(SubTotals)

Open in new window

0
 
amillyardAuthor Commented:
pratima_mcs:  am getting the error as follows:

Arithmetic overflow error converting expression to data type int.
0
 
Pratima PharandeCommented:
SELECT COUNT(RecordID) AS Frequency, ChargeableAmount AS ChargeableAmounts, SUM (ChargeableAmount) AS SubTotals ,
CAST( (select  sum(ChargeableAmount) from dbo.abcData) as bigint) as grandtotal

FROM dbo.abcData

WHERE (TransactionKey = 8)
  AND (TransactionTime BETWEEN '2011-03-01 00:00:00.000' AND '2011-03-01 23:59:59.999')
 
GROUP BY ChargeableAmount
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.