mysql sum returning wrong value

0 Views

Hi there so i have this problem in the following query, inside wp_wti_like_post i have number of rows with similar post_id and with value column as 1 or -1.
So let’s say for post with id 727 i have only one row , so the sum should be 1, but don’t know why it does return 4 and when there are two rows with 727 it does return 8 query is below: 

SELECT wp_posts. * , 
       SUM( wp_wti_like_post.value ) -4 AS total_sum,
       wp_wti_like_post.post_id
FROM wp_posts
INNER JOIN wp_term_relationships ON ( wp_posts.ID =  
                                        wp_term_relationships.object_id ) 
INNER JOIN wp_term_taxonomy ON ( wp_term_relationships.term_taxonomy_id =  
                                   wp_term_taxonomy.term_taxonomy_id ) 
JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) 
LEFT JOIN wp_wti_like_post ON ( wp_posts.ID = wp_wti_like_post.post_id    ) 
WHERE wp_term_taxonomy.taxonomy = 'category'
AND wp_term_taxonomy.term_id IN ('$c_cid')
AND wp_posts.post_type = 'post'
AND (wp_posts.post_status = 'publish')
GROUP BY wp_posts.ID
HAVING SUM( wp_wti_like_post.value )  > $min_like
ORDER BY wp_posts.post_date DESC
Post Views: 0

Related posts

Leave a Reply

1 comment

  1. You are using joins among many tables and there may b a chance that any of them has many associations for a post_id therefore your sum is incorrect i suggest you to use a sub select for your like table and calculate sum in sub select and then join with your main query 

    SELECT 
  p.*, COALESCE(l.sum_like,0) AS total_sum,
  l.post_id 
FROM
  wp_posts p 
  INNER JOIN wp_term_relationships ttr 
    ON (p.ID = ttr.object_id) 
  INNER JOIN wp_term_taxonomy tt 
    ON (ttr.term_taxonomy_id = tt.term_taxonomy_id) 
  LEFT JOIN (
      SELECT post_id ,SUM(`value`) sum_like
      FROM wp_wti_like_post 
      GROUP BY post_id
  ) l ON (p.ID = l.post_id) 
WHERE tt.taxonomy = 'category' 
  AND tt.term_id IN ('21') 
  AND p.post_type = 'post' 
  AND (p.post_status = 'publish') 
HAVING total_sum > 2 
ORDER BY p.post_date DESC

    Also note i have removed wp_postmeta join because its not used in your selection criteria and neither in your filter criteria also wp_postmeta stores different attributes for each post so i guess this table is producing more rows thats why wrong sum is calculated.

    Removed -4 from query