SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    SitePoint Addict
    Join Date
    Feb 2007
    Posts
    230
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SELECT MAX from select sum(...)+select sum(...)

    I am having a lot of problems with the following structure. Please could anybody help me with this.

    Code:
                SELECT
                    MAX(intermediate.test)
                FROM               
                  (
                  
                  SELECT SUM(table.field) FROM tabledfsd INNER JOIN ....
                  +
                  SELECT SUM(table.field) FROM tabledfsd INNER JOIN ....
                  
                  ) intermediate
    
    //or simplier example
    
    SELECT
                    MAX(intermediate.test)
                FROM               
                  (
                  
                  SELECT SUM(1+4)
                  +
                  SELECT SUM(2+6)
                  
                  ) intermediate
    What is a correct syntax for this? Is even possible to calculate two selects inside subquery?

    tnx!
    Dilster.com - Dating blog where you can find new dating tips, advices and ideas or your partner.

  2. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    I.. think you're looking at a UNION syntax... but perhaps you should explain what you're trying to do - I have a sneaking suspicion you're doing more work than you need to.

    EDIT: Note to self, read which forum you're in. Additional question: Which Database daemon are you using?

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    ernie, you forgot to assign a column alias in the subquery

    plus, it really does sound like you want UNION ALL instead of "plus"

    reason? because SUM() + SUM() gives one number as the answer, so why would you be wanting to take the MAX of a single number?
    Code:
    SELECT MAX(intermediate.test)
      FROM ( SELECT SUM(table.field) AS test
               FROM tabledfsd INNER JOIN ....
             UNION ALL
             SELECT SUM(table.field) 
               FROM tabledfsd INNER JOIN ....
           ) AS intermediate
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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