Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Summary Report

Posted on 2008-10-07
1
Medium Priority
?
1,007 Views
Last Modified: 2012-05-05
Hello experts,
I'm running SQL Server 2000.  I need an sql query to add a row (similar to the grand total) to this query.  Net deposit should add together the two fee columns and then subtract it from the rollup value.  

Attached (current) is where I am so far....
My Goal is attached (goalview)

Thanks!



SELECT     
COALESCE (CAST(SubscriberID AS varchar(8)), 'Grand Total:') AS SubscriberID, 
SUM(PaymentAmount) AS SubcriberTotal, 
SUM(PaymentAmount) * 0.0289 AS CreditCardFee, 
COUNT(PaymentID) * 0.30 AS TransactionFee
 
FROM  dbo.vTransactions
 
GROUP BY SubscriberID WITH rollup

Open in new window

SnapShot.png
GoalView2.png
0
Comment
Question by:thomasgroup
1 Comment
 
LVL 42

Accepted Solution

by:
dqmq earned 2000 total points
ID: 22664019
Try this:

SELECT    
COALESCE (CAST(SubscriberID AS varchar(8)), 'Grand Total:') AS SubscriberID,
SUM(PaymentAmount) AS SubcriberTotal,
(SUM(PaymentAmount) - (SUM(PaymentAmount) * 0.0289) - (COUNT(PaymentID) * 0.30) As Net,  
SUM(PaymentAmount) * 0.0289 AS CreditCardFee,
COUNT(PaymentID) * 0.30 AS TransactionFee
FROM  
dbo.vTransactions
 GROUP BY SubscriberID WITH rollup
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

926 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