CODEWITHSUNDEEP
phpmysql
Roland Johansson
Roland Johansson

Reputation: 101

find the three most sold products every day php mysql

I want to find the three most sold products every day and show them together with number of sales.

However if there are more than one product sharing the same number of sales I just want to tell how many products got this ranking.

I have two tables

Products:
+-----+---------+
| Pid | Product |
+-----+---------+
|  1  | Moon    |
|  2  | Sun     |
|  3  | Venus   |
|  4  | Mars    |
+-----+---------+

SalesRows:
+-----+---------+------------+
| Pid | No_sold | Sales_date |
+-----+---------+------------+
|  1  |    1    | 2013-01-01 |
|  2  |    5    | 2013-01-01 |
|  3  |    2    | 2013-01-01 |
|  2  |    2    | 2013-01-01 |
+-----+---------+------------+

Should give:

+------+--------------+-------+
| Rank | Product      | Sales |
+------+--------------+-------+
|  1   | Sun          |   7   |
|  2   | Venus        |   2   |
|  3   | Moon         |   1   |
+------+--------------+-------+

However this sales data:

SalesRows:
+-----+---------+------------+
| Pid | No_sold | Sales_date |
+-----+---------+------------+
|  1  |    1    | 2013-01-01 |
|  2  |    5    | 2013-01-01 |
|  3  |    2    | 2013-01-01 |
|  2  |    2    | 2013-01-01 |
|  4  |    1    | 2013-01-01 |
+-----+---------+------------+

Should give:

+------+--------------+-------+
| Rank | Product      | Sales |
+------+--------------+-------+
|  1   | Sun          |   7   |
|  2   | Venus        |   2   |
|  3   | *2 products* |   1   |
+------+--------------+-------+

Any suggestions how to solve this last part?

Upvotes: 1

Views: 1584

Answers (2)

Dhinakar
Dhinakar

Reputation: 4151

This query may help you.

SELECT @rownum := @rownum + 1 rownum, 
       t.* 
  FROM (SELECT @rownum:=0) r, 
       (select case when indicator = 1 then Product
else concat( indicator, ' Products') end as Product, sales from (Select *, count(sales) as indicator from (SELECT Product,SUM(No_sold) AS sales FROM SalesRows
JOIN Products ON Products.Pid = SalesRows.Pid
WHERE Sales_date = curdate()
GROUP BY SalesRows.Pid ) a group by sales Order by sales desc) a) t

Upvotes: 2

Prasanth Bendra
Prasanth Bendra

Reputation: 32710

Try this :

SELECT Product,SUM(No_sold) AS sales FROM SalesRows 
LEFT JOIN Products ON Products.Pid = SalesRows.Pid
WHERE Sales_date = '".$today."'
GROUP BY Pid

Upvotes: 0

Related Questions