1

The query's objective in brief is to list all the posts according to its score, which is calculated according to a calculate scores of date, views, and rating

The problem with the query is that it only give 1 row result instead of showing all of the posts.

I think the problem is with using MAX() and MIN() without separating it from the LEFT JOIN query used to get the SUM() of the Rating and the Views.

THE MCVE http://sqlfiddle.com/#!9/70d1ec/1

SELECT
          p.id
         , p.date
         , p.title
         , r.module
         , r.module_id
         , IFNULL(v.total_views,0)  AS views
         , r.total_rating           AS rating

     #the following formula calculates the Score 10*(MAX(points)-(points))/(MAX(points)-MIN(points))

    , round((round(10-(((PD.MaxDate-p.date)/(PD.MaxDate-PD.MinDate))*10), 3) + round(10-(((MAX(v.total_views)-v.total_views)/(MAX(v.total_views)-MIN(v.total_views)))*10), 3) + round(10-(((MAX(r.total_rating)-r.total_rating)/(MAX(r.total_rating)-MIN(r.total_rating)))*10), 3))/3, 3) AS Score


  FROM  posts p

  LEFT  JOIN ( SELECT ra.module_id
              , ra.module   AS module
              , SUM(ra.ilike)    AS total_rating
           FROM rates ra
          WHERE ra.module = 'posts'
          GROUP
             BY ra.module_id
       ) r ON r.module_id = p.id

  LEFT  JOIN ( SELECT pv.post_id
              , SUM(1)    AS total_views
           FROM posts_views pv
          GROUP
             BY pv.post_id
       ) v ON v.post_id = p.id

   JOIN (SELECT MIN(date) AS MinDate, MAX(date) AS MaxDate FROM posts) PD


    ORDER BY Score DESC
0

Since you have aggregate functions in your SELECT clause, you need to specify what groups to apply them to. If you add:

GROUP BY   p.id
 , p.date
 , p.title
 , v.total_views
 , r.total_rating
 , r.module
 , r.module_id

before ORDER BY in your query, you will get 1 row per such group.

Without a GROUP BY clause, MySQL will randomly pick 1 row for you. This is a well-known bug in older MySQL versions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.