# Thread: Sum With Records Displayed?

1. Registered User
Join Date
Dec 2010
Posts
36

## Unanswered: Sum With Records Displayed?

Is it possible to use the sum function to not only display the sum of values in a column but also show all the records which meet that condition? e.g.

Code:
```SELECT*
FROM TransferFees
WHERE Fee BETWEEN '10.00' AND '30.00'```
Shows the records which meet the condition and :

Code:
```SELECT SUM(Fee)
FROM TransferFees
WHERE Fee BETWEEN '10.00' AND '30.00'```
Shows the sum of all values between 10.00 and 30.00 but can I do both, i.e. show the records and the sum in the same query?

I tried :

Code:
```SELECT*
FROM TransferFees
WHERE Fee BETWEEN '10.00' AND '30.00'
SELECT SUM(Distinct Fee) AS Total FROM TransferFees```
But the total given isn't correct. I copied the results in Excel and added the values but got a different total from the one given in SQL.

2. Registered User
Join Date
May 2009
Posts
509
Palermo, This best done in your reporting software. But here is one way you can do it with SQL:
Code:
```WITH DATA_TAB (FEE_TYPE, FEE)
AS (SELECT 'A',  1.00 UNION ALL
SELECT 'A', 10.00 UNION ALL
SELECT 'A', 15.00 UNION ALL
SELECT 'A', 20.00 UNION ALL
SELECT 'A', 25.00 UNION ALL
SELECT 'A', 30.00 UNION ALL
SELECT 'A', 35.00 UNION ALL
SELECT 'B', 10.00 UNION ALL
SELECT 'B', 20.00 UNION ALL
SELECT 'B', 30.00 UNION ALL
SELECT 'B', 35.00
)
, SEL_RESULT (DATA_TYPE, FEE_TYPE, FEE)
AS (SELECT ' ', FEE_TYPE, FEE
FROM DATA_TAB
WHERE FEE BETWEEN 10.00 AND 30.00
)
, SUM_RESULT (DATA_TYPE, FEE_TYPE, FEE)
AS (SELECT 'TOTAL', FEE_TYPE , SUM(FEE)
FROM SEL_RESULT
GROUP BY FEE_TYPE, DATA_TYPE
)
SELECT DATA_TYPE, FEE_TYPE, FEE
FROM SEL_RESULT
UNION ALL
SELECT DATA_TYPE, FEE_TYPE, FEE
FROM SUM_RESULT
ORDER BY FEE_TYPE, DATA_TYPE

DATA_TYPE FEE_TYPE FEE
--------- -------- ---------------------------------
A                                    30.00
A                                    25.00
A                                    20.00
A                                    15.00
A                                    10.00
TOTAL     A                                   100.00
B                                    30.00
B                                    20.00
B                                    10.00
TOTAL     B                                    60.00```

3. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Oh, the trouble you're about to cause yourself!
Code:
```SELECT fee
FROM (SELECT 0.1 * number AS fee
FROM master.dbo.spt_values AS z
WHERE  'P' = [type]) AS a
WHERE fee BETWEEN 10 and 30
COMPUTE Sum(fee)```
-PatP

4. Registered User
Join Date
Dec 2010
Posts
36
That's not what I'm trying to achieve! This should help :

http://img.villagephotos.com/p/2010-...2010_Forum.gif

That's what I want to replicate, if possible, in SQL.
Last edited by Palermo; 12-30-10 at 23:08.

5. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Originally Posted by Palermo
That's not what I'm trying to achieve!
That seems structurally exactly like the result that I posted. Only the cosmetics are different, and those could be fixed with whatever presentation software you use (such as SSRS).

-PatP

6. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Note that the fact that I can produce what you requested using SQL emphatically does not make it a good idea. This ought to be done with your reporting package.

-PatP

7. Registered User
Join Date
Aug 2009
Posts
262
i think he is not using any reporting software .

8. Registered User
Join Date
Dec 2010
Posts
36
Correct. I have only been playing around with SQL for a week so I am not sure what can and cannot be achieved.

9. Registered User
Join Date
Nov 2004
Posts
1,427
Code:
```SELECT *
FROM TransferFees
WHERE Fee BETWEEN '10.00' AND '30.00'
SELECT SUM(Distinct Fee) AS Total FROM TransferFees
WHERE Fee BETWEEN '10.00' AND '30.00' -- is missing```
Part of the problem is the use of DISTINCT in the SUM function, it will only count different fees (the three times the fee 14.00 was paid, will end up as 14.00 in the sum, instead of 42.00).

The WHERE clause is missing in the second SELECT. So the first SELECT will show different records from the ones used to calculate the SUM.

This should work (supposing that MSSQL will generate sums of text columns and not throw an ERROR).
Code:
```SELECT
0 as Nr_order, Player, Position, Previous_Club, Next_Club, Fee, League, Window
FROM PlayerTransfer
WHERE Fee BETWEEN '10.00' AND '30.00'

UNION ALL

SELECT
1 as Nr_order, 'Sum', NULL, NULL, NULL, SUM(Fee), NULL, NULL
FROM PlayerTransfer
WHERE Fee BETWEEN '10.00' AND '30.00'
ORDER BY Nr_order, Fee```
The column Nr_order is artificially added to make it possible to put the SUM line after the records with the transfer fees.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•