Results 1 to 7 of 7

Thread: using SUM

  1. #1
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104

    Unanswered: using SUM

    Hi

    I have an SQL query:

    Code:
    SELECT item_number,
    		CASE
    			WHEN item_number LIKE '%-3'
    				THEN SUM(mc_fee)
    			WHEN item_number LIKE '%-6'
    				THEN SUM(mc_fee / 2)
    			WHEN item_number LIKE '%-15'
    				THEN SUM(mc_fee / 5)
    			WHEN item_number LIKE '%-30'
    				THEN SUM(mc_fee / 10)
    			ELSE NULL
    		END AS total_charges
    	FROM ut_Conversations AS C INNER JOIN ut_Transactions AS T ON C.payment_id = T.payment_id
    	WHERE [client_user_id] <> [reader_user_id] AND MONTH(conversation_end) = MONTH(@Date)
    	GROUP BY item_number
    This produces a table, for example:

    item_number total_charges
    bm-15 0.71
    bm-3 6.00
    bm-6 3.23
    pl-15 1.85
    pl-3 28.24
    pl-6 17.91

    My question is, how would i go about getting the SUM of the total_charges column, (57.94) - do i still use a CASE or something else to work out the charges?

    thanks

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    how about sum(case...end)?
    Dave

  3. #3
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    Hi Dave

    I did try that but I got an error:

    Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

    .....stupidly i had left the SUM() functions within the case so removed it and it worked.

    thanks

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mattock View Post
    My question is, how would i go about getting the SUM of the total_charges column, (57.94)
    Code:
    SELECT SUM(total_charges) AS total_total_charges
      FROM ( SELECT item_number
                  , SUM(mc_fee) / 
                      CASE WHEN item_number LIKE '&#37;-3'  THEN 1
                           WHEN item_number LIKE '%-6'  THEN 2
                           WHEN item_number LIKE '%-15' THEN 5
                           WHEN item_number LIKE '%-30' THEN 10 
                      ELSE NULL END 
                    AS total_charges
               FROM ut_Conversations AS C 
             INNER 
               JOIN ut_Transactions AS T  
                 ON T.payment_id = C.payment_id
              WHERE [client_user_id] <> [reader_user_id] 
                AND MONTH(conversation_end) = MONTH(@Date)
             GROUP 
                 BY item_number ) AS q
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2006
    Location
    Leeds, UK
    Posts
    104
    I guess you learn something new every day.....didn't know you could use a 'SELECT' instead of a table! Thanks rudy

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It's called a derived table, and Rudy has demonstrated some best practice for your type of problem.

    Here is some more
    Data belongs in your tables -- not in your code
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    In fact, rereading I'd be tempted to calculate the value (by dividing your suffix by 3) than store this in a table, assuming this calculation is correct.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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