Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2010
    Posts
    36

    Question 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. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    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. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    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-31-10 at 00:08.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Palermo View Post
    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).

    Please explain what I'm missing.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

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

  8. #8
    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. #9
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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