CODEWITHSUNDEEP
mysqlsqlsqlite
Eme Emertana
Eme Emertana

Reputation: 571

Not sure how to write a simple query

Hi I Have a table which has number of products and rating for each, due to some reasons I have different row of each product along with rate of each person who rated it, as following:

p1 2
p2 4
p3 4
p1 4
p1 5
p1 3
p2 7

I am using following query but it shows the average rate of all products, but I am expecting it to show the average rate of each product seperately.

Select ProductName, AVG(Rate) FROM Products

Upvotes: 2

Views: 80

Answers (2)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79979

Just GROUP BY productName:

Select ProductName, AVG(Rate) AS 'Average Rate'
FROM Products 
GROUP BY productName

Edit: To show the one with highest rate:

SELECT ProductName, Rate
FROM Products
ORDER BY rate DESC
LIMIT 1

Edit 2: To get the product name with the highest average rate:

SELECT productname, AVG(rate) Avgrate
FROM Products
GROUP BY productname 
HAVING AVG(rate) = 
(
     SELECT AVG(rate) 
     FROM Products 
     GROUP BY productname 
     ORDER BY AVG(rate) DESC
     LIMIT 1
)

Upvotes: 2

Jignesh Thakker
Jignesh Thakker

Reputation: 3698

You need to add Group By.

Select ProductName, AVG(Rate) FROM Products GROUP BY ProductName

To Sort Result use Order By after Group By 

Select ProductName, AVG(Rate) FROM Products GROUP BY ProductName ORDER BY ProductName

Above query sort result in Ascending order if you want to sort descending then use DESC,

Select ProductName, AVG(Rate) FROM Products GROUP BY ProductName ORDER BY ProductName DESC

To Select Higher Rating Product

Select ProductName, AVG(Rate) FROM Products GROUP BY ProductName
HAVING AVG(Rate) = (SELECT MAX(AVG(Rate)) FROM Products GROUP BY ProductName)

This query return all product which have max average rating.

Only One Product with Higher rating use LIMIT in Descending Sort

 Select ProductName, AVG(Rate) FROM Products GROUP BY ProductName 
 ORDER BY ProductName DESC
 LIMIT 1

Upvotes: 2

Related Questions