Sanjay Nakate Sanjay Nakate - 5 months ago 14
SQL Question

Select sum top 3 of last 7 days

I have bellow snippet table.

rating table

Above the rating table i am saving the records of

vote
of
product_id
with
user_id
who have rated to that
product_id
with vote and
rating_date
.

Now here i have to fetch top 3 product_id who have max sum of vote in last 7 days (from current date).

Out put expected for max vote sum record of product_id :-

1)
product_id
8965 have 403 max
vote
of sum of last 7 days

2)
product_id
4024 have 36 max
vote
of sum of last 7 days

3)
product_id
2015 have 5 max
vote
of sum of last 7 days

This is all the top 3 max sum of vote of last 7 days so i have fetch
produt_id
according to this output.

I have tried bellow snippet code but its wrong something.

<?php
$query = mysql_query("SELECT SUM(vote) AS 'meta_sum', product_id
FROM rating
where rating_date >= curdate() - INTERVAL '7' DAY
AND rating_date < curdate() - INTERVAL '1' DAY
GROUP BY product_id
ORDER BY meta_sum DESC LIMIT 3") OR DIE(mysql_error());
while($row = mysql_fetch_assoc($query))
{
$topexpert =$row["product_id"];
$toprating =$row["meta_sum"];
?>

Answer

Please replace with these query

SELECT SUM(vote) AS 'meta_sum', product_id 
FROM rating 
where rating_date BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND NOW()
GROUP BY product_id 
ORDER BY meta_sum DESC LIMIT 3