SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Evangelist
    Join Date
    Mar 2003
    Location
    Melbourne, Australia
    Posts
    463
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    select merge where id is the same?

    hi all

    this is a pretty long query
    what i am trying to do, is select the five people that have voted me most
    the problem is, there are two tables which contain votes
    one uservotelog table contains all normal votes
    one supervote table contains all supervotes

    the following query can select all those that have voted for me
    and order them according to most votes
    but, if someone has done supervotes and normal votes, they will appear twice
    is it possible to add them together inside sql without affecting performance,
    rather than having to resort to some php array's and sorting?

    <--mysql 4.1.7-->
    Code:
        SELECT user_ID, user_Nick, sum
       FROM (
       SELECT user_ID, user_Nick, SUM( uservotelog_VoteValue )  AS sum
       FROM urban_userlog
       INNER  JOIN urban_uservotelog ON user_ID = uservotelog_VoterUserID
       AND user_AccountStatus =  'a'
       AND uservotelog_Type <>  'S'
       AND uservotelog_VoteeUserID =  '1'
       GROUP  BY user_Nick
       UNION 
       SELECT user_ID, user_Nick, (COUNT( usersupervote_UserID )  *20
       ) AS sum
       FROM urban_usersupervote
       INNER  JOIN urban_userlog ON user_ID = usersupervote_UserID
       WHERE usersupervote_Gender =  'guys'
       AND usersupervote_Date >=  '2005-02-01'
       GROUP  BY user_ID
       ) AS mostvotes
       ORDER  BY sum DESC
    sample output
    user_ID--- user_Nick---- votes
    1 --------- fred --------- 12
    2 ---------bill ---------12
    3 ---------tom ---------11
    5 ---------jo ---------10
    8 ---------liz--------- 8
    9 ---------greg --------- 7
    14 ---------frank ---------7
    8 --------- liz ---------6
    3 --------- tom --------- 2

    where tom and liz show up twice, for example they both did supervotes and normal votes
    and their accumulated vote totals would be 13 and 14 respectively
    and should therefore be at the top of the table, and only show up once

    thanks
    dave
    Last edited by dtra; Feb 1, 2005 at 00:22.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT user_ID
         , user_Nick
         , sum(subtotal) as total
      FROM (
           SELECT user_ID
                , user_Nick
                , SUM( uservotelog_VoteValue )  AS subtotal
             FROM urban_userlog
           INNER  
             JOIN urban_uservotelog 
               ON user_ID = uservotelog_VoterUserID
              AND user_AccountStatus =  'a'
              AND uservotelog_Type <>  'S'
              AND uservotelog_VoteeUserID =  '1'
           GROUP
               BY user_ID
                , user_Nick
           UNION ALL
           SELECT user_ID
                , user_Nick
                , COUNT(usersupervote_UserID)*20  
             FROM urban_usersupervote
           INNER
             JOIN urban_userlog 
               ON user_ID = usersupervote_UserID
           WHERE usersupervote_Gender =  'guys'
             AND usersupervote_Date >=  '2005-02-01'
           GROUP
              BY user_ID
               , user_Nick
          ) AS mostvotes
    GROUP
        BY user_ID
         , user_Nick      
    ORDER
        BY total DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Mar 2003
    Location
    Melbourne, Australia
    Posts
    463
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks mate
    that looks like it's going to do the trick
    i'll break it down and study it
    with the subtotal alias
    is it because of union all that the part for supervotes comes under subtotal?

    and also, group by multiple columns
    is that faster? or does that just ensure better accuracy

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by dtra
    with the subtotal alias
    is it because of union all that the part for supervotes comes under subtotal?
    sorry, i don't understand the question

    i changed the alias because SUM is a reserved word

    i used UNION ALL so that you don't lose data!!

    Quote Originally Posted by dtra
    and also, group by multiple columns
    is that faster? or does that just ensure better accuracy
    i'm a stickler on this issue, which you need not get involved in, but according to the standards, you must GROUP BY all non-aggregates in the SELECT list, even though mysql has a proprietary extension which bypasses the standard (on the whole, an egregiously poor decision, as i have seen many many more instances where sql developers were hurt by this "misfeature" than were helped by it)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist
    Join Date
    Mar 2003
    Location
    Melbourne, Australia
    Posts
    463
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oh, yeah it's good to stick with standards whenever possible
    the subtotal thing was because it is used as an alias for the first part of the union
    and not in the second, i thought it needed to be, i guess it just has to be the same column type?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    yes

    in a UNION, all the SELECTs in the UNION must have the same number of columns, and these must be datatype-compatible (number with number, string with string, date with date, etc.)

    the UNION result set has column names and datatypes taken from the first SELECT

    this is why in some databases, if you do this --
    Code:
    select pkey
         , 'complete' as status
         , sum(balance) as total
      from ...
     where ...
    union all  
    select id
         , 'incomplete' 
         , sum(total)
      from ...
    where ...
    you will find rows in the result with a status of 'incomple'
    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
  •