CODEWITHSUNDEEP
mysqljoin
Uhehesh
Uhehesh

Reputation: 516

MySQL join two tables, find max count and order by

I am rather newbie in SQL and before this moment I only used simple queries, but now I have a problem. I have two tables. First is rating:

id  userid  value 
1   3       +
1   2       +
1   2       +

And second is daybook:

id  userid  week    day     lesson  content 
1   2       1       1       6       Test!

So now I have a problem. I need to:

1) Join these tables by id.

2) Then order results by count of entries in rating table.

So result must look like:

userid count
3      1
2      2

How to do that? Thanks for anything helpful.

Upvotes: 1

Views: 2574

Answers (2)

jzworkman
jzworkman

Reputation: 2703

select daybook.userid, count(*) as count 
    from daybook, rating 
    where daybook.userid = rating.userid
    group by daybook.userid
    order by count desc

But you dont even really need the daybook table:

select userid, count(*) as count
   from rating
   group by userid
   order by count desc

Upvotes: 3

DarkAjax
DarkAjax

Reputation: 16223

You need to do something like this:

SELECT count(id), rating.userid
FROM rating
JOIN daybook ON daybook.userid = rating.userid
GROUP BY userid

Upvotes: 2

Related Questions