SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Guru johnjohn2's Avatar
    Join Date
    Apr 2004
    Location
    here
    Posts
    746
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    union query question

    Using postgresql 7.4

    I'm attempting my first union query in my site. Is this even close to being correct syntax?

    SELECT Sum(tbl95.col28) AS SumOfcol28, "cleared" AS status
    FROM tbl95
    GROUP BY tbl95.col2, tbl95.col13, tbl95.col17, tbl95.col39
    HAVING ((tbl95.col2=8478) AND (tbl95.col17=0) AND (tbl95.col39 Is Not Null))


    UNION ALL SELECT Sum(tbl95.col28) AS SumOfcol28, "voided" AS status
    FROM tbl95
    GROUP BY tbl95.col2, tbl95.col13, tbl95.col17, tbl95.col39
    HAVING ((tbl95.col2=8478) AND (tbl95.col17=1))


    UNION ALL SELECT Sum(tbl95.col28) AS SumOfcol28, "outstanding" AS status
    FROM tbl95
    GROUP BY tbl95.col2, tbl95.col13, tbl95.col17, tbl95.col39
    HAVING ((tbl95.col2=8478) AND (tbl95.col17=0) AND (tbl95.col39 Is Null));

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    two problems come to mind

    first, when you say

    ... GROUP BY tbl95.col2, tbl95.col13, tbl95.col17, tbl95.col39

    this will result in one row per every distinct set of values

    for example,
    Code:
    col2 col13 col17 col39
     1     A    34    101  
     1     A    34    102  
     1     A    34    103  
     1     A    37    101  
     1     A    37    102
     1     A    37    103  
     1     B    51    101  
     1     B    52    101         
     2     A    34    101          
     2     A    34    102
    now, this in itself isn't bad, but none of these columns are in the SELECT list!!

    therefore, the results for the above GROUP BY might be --

    SumOfcol28
    937
    45
    62
    45
    9
    234
    101
    55
    36
    76

    which, as far as i can tell, isn't going to be all that useful

    the second problem isn't actually a problem, it'll work, but it's not efficient

    all your HAVING conditions should actually be in the WHERE clause, because they are conditions on columns that are actually used in the GROUP BY

    which brings us back the the GROUP BY

    if you are going to require that a certain column have only a specific value, then there's no point in including it in the GROUP BY, since it will have only one value in any group

    this would reduce the GROUP BY to only col13

    however, the first point still stands, for each distinct value of col13 you will get a sum, but no way on knowing which sum goes with which value of col13

    helps?
    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
  •