#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2011
    Posts
    53
    Rep Power
    4

    Using a calculated column in the same query


    Hi all

    I'm sure this has been asked before but I can't find any obvious answers.

    This query doesn't work; I'm going to try a subquery but is there a better way to achieve this?

    Code:
      SELECT DISTINCT ID
       , sum (charge1) 
       + sum (charge2) 
       + sum (charge3) 
       - sum (refund1) AS revenuetotal
        FROM contracts
       WHERE date BETWEEN '2012-04-01' AND '2012-04-30'
         AND revenuetotal != 0
         AND customerid='123' 
    GROUP BY ID
    thank you
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2011
    Posts
    53
    Rep Power
    4
    I think I've solved this:

    Code:
      SELECT DISTINCT ID
       , sum (charge1) 
       + sum (charge2) 
       + sum (charge3) 
       - sum (refund1) AS revenuetotal
        FROM contracts
       WHERE date BETWEEN '2012-04-01' AND '2012-04-30'
         AND revenuetotal != 0
         AND customerid='123' 
    GROUP BY ID
      HAVING (sum (charge1)
             +sum (charge2)
             +sum (charge3)
             -sum (refund1)) <> 0
    At least, it works - I'm not sure if it's the most efficient solution, any comments welcome.

    Thanks
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,172
    Rep Power
    4274
    you could also use the inline view approach
    Code:
    SELECT id
         , revenuetotal
      FROM ( SELECT id
                  , sum (charge1) +
                    sum (charge2) + 
                    sum (charge3) - 
                    sum (refund1) AS revenuetotal
               FROM contracts
              WHERE date BETWEEN '2012-04-01' AND '2012-04-30'
                AND customerid = '123' 
             GROUP 
                 BY id ) AS v
     WHERE revenuetotal <> 0
    this is a useful technique whenever you need to reference a complex expression using a column alias in the WHERE clause when there is no GROUP BY clause that allows you to use HAVING

    but the main reason i'm replying is to tell you that your use of DISTINCT was redundant

    using GROUP BY produces distinct rows by definition
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2011
    Posts
    53
    Rep Power
    4
    Thanks Rudy

    That's a really useful technique, as you say. Am I right in thinking that the use of 'v' as the name of the view is just an arbitrary character, in this case being the initial of view? Could it just as easily be 'bananas' or whatever, provided it's a unique string?

    Also, thank you for explaining how DISTINCT is not required. I didn't realise that GROUP BY has the same effect.

IMN logo majestic logo threadwatch logo seochat tools logo