SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Evangelist optl's Avatar
    Join Date
    Oct 2004
    Location
    Washington DC
    Posts
    415
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problem with multiple table query using group by

    I am running MySQL version 5.0.27-standard-log

    Code:
     SELECT COUNT( rant_rants.* )
    FROM rant_rants, rant_users
    GROUP BY rant_rants.userid
    LIMIT 0 , 15
    Why won't this work? Foreach each rant_rants.userid I want the total number of rows in rant_rants with that userid. I have the second table in there because I am going to add more to this query once I figure this out. Any ideas?
    Last edited by optl; Jun 30, 2007 at 08:09.
    For the phrase "Bethesda home architect", my clients
    websites occupy 6 of the first 8 results
    on the 1st page of Google. My Secret SEO Strategy Revealed

  2. #2
    SitePoint Member
    Join Date
    Jun 2007
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think when you use group by user id, you cant retrieve other values other than the group by field and count.

    I think you can sort it out using a sub query which retrieves the distinct user id and use them as input for the first query.

  3. #3
    SitePoint Member
    Join Date
    Jun 2007
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    some thing like this.

    select count(rant_rants.*) from rant_rants where user id in(select distinct(user id) from rant_rants)

    Correct me if am wrong

  4. #4
    SitePoint Evangelist optl's Avatar
    Join Date
    Oct 2004
    Location
    Washington DC
    Posts
    415
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by satyojath1 View Post
    some thing like this.

    select count(rant_rants.*) from rant_rants where user id in(select distinct(user id) from rant_rants)

    Correct me if am wrong
    That gives me the same error I get:
    #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) from rant_rants where userid in(select distinct(userid) from rant_rants)' at line 1
    For the phrase "Bethesda home architect", my clients
    websites occupy 6 of the first 8 results
    on the 1st page of Google. My Secret SEO Strategy Revealed

  5. #5
    SitePoint Member
    Join Date
    Jun 2007
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think the problem is with the count(rant_rants.*), try using without appending the table name.

  6. #6
    SitePoint Evangelist optl's Avatar
    Join Date
    Oct 2004
    Location
    Washington DC
    Posts
    415
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If I do that I don't get the correct number because it counts from both tables.
    For the phrase "Bethesda home architect", my clients
    websites occupy 6 of the first 8 results
    on the 1st page of Google. My Secret SEO Strategy Revealed

  7. #7
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you won't get the correct number of rows because you are selecting from two tables without specifying a column to join on from each table. without that you are going to get the product of the rows instead. 10 rows in one table and 10 in another gives you 100 rows when you don't specify a join clause.

    please also read the sticky thread at the top of the forum since you are using an outdated version of mysql.

  8. #8
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Since you're not using the rant_users table anywhere, why is it even in the query?

    Code:
    SELECT userid, COUNT(*)
    FROM rant_rants
    GROUP BY userid
    ORDER BY COUNT(*) DESC
    LIMIT 15

  9. #9
    SitePoint Evangelist optl's Avatar
    Join Date
    Oct 2004
    Location
    Washington DC
    Posts
    415
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am going to I just removed the parts where I do to simplify the query for this thread.

    Can you tell me more about how to join on a certain column?
    For the phrase "Bethesda home architect", my clients
    websites occupy 6 of the first 8 results
    on the 1st page of Google. My Secret SEO Strategy Revealed

  10. #10
    SitePoint Evangelist optl's Avatar
    Join Date
    Oct 2004
    Location
    Washington DC
    Posts
    415
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Any ideas on how I should join the two tables?
    For the phrase "Bethesda home architect", my clients
    websites occupy 6 of the first 8 results
    on the 1st page of Google. My Secret SEO Strategy Revealed

  11. #11
    SitePoint Evangelist optl's Avatar
    Join Date
    Oct 2004
    Location
    Washington DC
    Posts
    415
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Figured out the solution:
    Code:
    SELECT
    	COUNT(userid) as total_rants,
    
    FROM
    	rant_rants
    	LEFT JOIN rant_users
    	ON rant_users.id = rant_rants.userid
    	
    GROUP BY
    	userid
    	
    ORDER BY
    	total_rants DESC
    For the phrase "Bethesda home architect", my clients
    websites occupy 6 of the first 8 results
    on the 1st page of Google. My Secret SEO Strategy Revealed

  12. #12
    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)
    optl, that cannot possibly be good enough

    you will get results that look like this --

    total_rants
    187
    123
    109
    99
    37

    what good is that without the userid?

    and it shouldn't be a LEFT OUTER JOIN because you shouldn't have any rows in rant_rants where the userid isn't in rant_users

    why are you joining the tables again?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by optl View Post
    I am going to I just removed the parts where I do to simplify the query for this thread.
    please don't do that. more often than not, this introduces other errors in to the query and makes it more difficult to figure out the problem.

  14. #14
    SitePoint Evangelist optl's Avatar
    Join Date
    Oct 2004
    Location
    Washington DC
    Posts
    415
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I simplified the query just so it would be easier to read. Here is the full query:
    Code:
    SELECT
    userid,
    COUNT(userid) as total_rants,
    SUM(unique_ratings) as ratings,
    SUM(clicks) as clicks,
    referrals,
    ROUND(SUM(total_rating) / SUM(unique_ratings)) as avg_rating,
    ROUND(((SUM(total_rating) / SUM(unique_ratings) * 3) * COUNT(userid)) + (SUM(clicks) / COUNT(userid)) + (SUM(saved) * (SUM(total_rating) / SUM(unique_ratings) / 2)) + (referrals * 23)) AS score
    FROM rant_rants LEFT JOIN rant_users ON rant_users.id = rant_rants.userid
    GROUP BY userid
    ORDER BY score DESC
    For the phrase "Bethesda home architect", my clients
    websites occupy 6 of the first 8 results
    on the 1st page of Google. My Secret SEO Strategy Revealed

  15. #15
    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)
    which table is the referrals column in?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Evangelist optl's Avatar
    Join Date
    Oct 2004
    Location
    Washington DC
    Posts
    415
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    rant_users sorry should have specified.
    For the phrase "Bethesda home architect", my clients
    websites occupy 6 of the first 8 results
    on the 1st page of Google. My Secret SEO Strategy Revealed

  17. #17
    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)
    Quote Originally Posted by optl View Post
    rant_users sorry should have specified.
    s'okay
    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
  •