CODEWITHSUNDEEP
mysqlleft-joinaggregate-functions
Kerry Jones
Kerry Jones

Reputation: 21838

MySQL - SUM/COUNT Different Columns From Different Tables

I have a peculiar problem. Given three tables.

product  
 - id
 - title

product_rating 
 - product_id
 - rating

product_view
 - product_id

I want to grab products with a SUM of their ratings (which will either be a +1 or -1 value) and their total product count.

SELECT 
    p.`id`, p.`title`,
    SUM( pr.`rating` ) AS rating, 
    COUNT( pv.`product_id` ) AS views 
FROM 
    `product` AS p 
    LEFT JOIN `product_rating` AS pr ON ( pr.`product_id` = p.`id` ) 
    LEFT JOIN `product_view` AS pv ON ( pv.`product_id` = p.`id` ) 
GROUP BY 
    p.`id`
ORDER BY rating DESC

In the table I have 1 rating, 9 views. The query, however, is returning 9 rating, 9 views. I understand why it's happening (it's summing the rating for each product_view), but I don't know how to get around it.

Any suggestions would be greatly appreciated.

Sample data:

product
------------
id | title  
1  | Globber  

product_rating
-------------------
product_id | rating  
1          | 1  

product_view
------------
product_id  
1  
1  
1  
1  
1  
1  
1  
1  
1

Upvotes: 0

Views: 149

Answers (2)

peterm
peterm

Reputation: 92785

Try

SELECT p.id, p.title, r.rating, v.views
  FROM product p LEFT JOIN
( 
  SELECT product_id, SUM(rating) rating
    FROM product_rating
   GROUP BY product_id
) r ON p.id = r.product_id LEFT JOIN
(
  SELECT product_id, COUNT(*) views
    FROM product_view
   GROUP BY product_id
) v ON p.id = v.product_id
ORDER BY r.rating DESC

Sample output:

| ID |   TITLE | RATING | VIEWS |
---------------------------------
|  1 | Globber |      1 |     9 |

Here is SQLFiddle demo

Upvotes: 3

502_Geek
502_Geek

Reputation: 2126

How to do that? 

SELECT tbl.pid,tbl.ptitle, SUM(tbl.rating) as Rate, COUNT (tbl.views) as ViewList FROM (SELECT 
p.`id` as pid, p.`title` as ptitle,
pr.`rating` AS rating, 
pv.`product_id` AS views 
 FROM 
`product` AS p 
LEFT JOIN `product_rating` AS pr ON ( pr.`product_id` = p.`id` ) 
LEFT JOIN `product_view` AS pv ON ( pv.`product_id` = p.`id` ) ) as tbl
GROUP BY 
tbl.`pid`
ORDER BY tbl.Rate DESC

Upvotes: 0

Related Questions