Solved

sql script - grant total

Posted on 2011-03-02
5
1,006 Views
Last Modified: 2012-08-13
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

0
Comment
Question by:amillyard
  • 3
5 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35016425
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
 
LVL 25

Expert Comment

by:reb73
ID: 35016467
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
 

Author Comment

by:amillyard
ID: 35016504
pratima_mcs:  am getting the error as follows:

Arithmetic overflow error converting expression to data type int.
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35016565
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
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 500 total points
ID: 35016583
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question