add a column in MySQL rank by deal by day

Question

I am just learning MySQL. I need to find out rank of deals by day. Here I am adding the corresponding MYSQL query for my requirement that currently ranks all sales highest to lowest by day. Please help me to add a column that gives the rank to the deal highest to lowest and resetting the next day.

Here is my current working query,..

single day with title, price

SELECT 
    DATE(order_items.created_at) AS the_day, 
    order_items.deal_id, 
    SUM(order_items.item_total) AS daily_total, 
    SUM(order_items.qty) AS units_sold, 
    deals.price,
    deals.title

FROM 
    order_items JOIN deals ON order_items.deal_id = deals.id 

WHERE 
    order_items.created_at >= '2016-01-01 00:00:00' AND order_items.created_at < '2016-01-30 00:00:00' 
    AND 
    order_items.status=1 
    AND 
    order_items.paid=1 

GROUP BY 
    order_items.deal_id 

ORDER BY 
    the_day, 
    daily_total DESC;

I also wonder if it is possible to limit just the top 5 results for each day? — run Eric Run, Jan 26 '16 at 22:06
Do you have to have it in a column? Or is it ok that you get it in php when you loop over the results? — Gavriel, Jan 26 '16 at 22:07
Gavriel - I do not know PHP yet, this is being exported into excel Gavriel - LIMIT 5 limits to only 5 results total, is there a way to get 5 per day? — run Eric Run, Jan 26 '16 at 22:14
This is not a PHP question, and using PHP to filter results is a really bad idea. — rybo111, Jan 26 '16 at 22:52
Yes, but having a counter from 1-5 "each day" when you loop over the results (that have only the top 5.per day) would be acceptable maybe. — Gavriel, Jan 26 '16 at 23:00

score 0 · Answer 1 · edited May 23 '17 at 12:15

The easiest way to do is that:

Use your existing SQL - I guess you need to update your SQL, make sure any non-aggregated columns in select should be in group by as well
Use PHP to loop (1-5), it works for multiple days

If you are happy to get top 5 for a single day, you can add limit 5 at end of your SQL

If you need top 5 results for each day in multiple days in one SQL, you need to update SQL to be more complicated. And here is a hint to use row id see example:

select increment counter in mysql

OK - Since you updated your question to return top 1 result per day, this is easier:

Step 1: get each day, each deal, report:

SELECT deal_id, date(created_at) ymd, sum(item_total) daily_total, sum(qty) units_sold
FROM order_items
WHERE substr(created_at,1,7) = '2016-01'
AND status = 1
AND paid   = 1
GROUP BY 1,2

Step 2: Find the best deal of each day from step 1:

SELECT aa.ymd, max(aa.daily_total) max_total
FROM (
    SELECT deal_id, date(created_at) ymd, sum(item_total) daily_total, sum(qty) units_sold
    FROM order_items
    WHERE substr(created_at,1,7) = '2016-01'
    AND status = 1
    AND paid   = 1
    GROUP BY 1,2
) as aa
GROUP BY 1;

Please note that max(item_total) not necessary same row as max(unit_sold), so you need to choose one, and cannot run togather

Step 3: Join step 2 with step 1 and deals to find out the rest of information:

SELECT aa.*, deals.price, deal.title
FROM (
    SELECT aa.ymd, max(aa.daily_total) max_total
    FROM (
        SELECT deal_id, date(created_at) ymd, sum(item_total) daily_total, sum(qty) units_sold
        FROM order_items
        WHERE substr(created_at,1,7) = '2016-01'
        AND status = 1
        AND paid   = 1
        GROUP BY 1,2
    ) as aa
    GROUP BY 1
) as bb
JOIN (
        SELECT deal_id, date(created_at) ymd, sum(item_total) daily_total, sum(qty) units_sold
        FROM order_items
        WHERE substr(created_at,1,7) = '2016-01'
        AND status = 1
        AND paid   = 1
        GROUP BY 1,2
) as aa ON bb.ymd = aa.ymd and bb.max_total = aa.daily_total
JOIN deals ON aa.deal_id = deals.id
ORDER BY aa.ymd, aa.max_total

K - How do I do option 1? – run Eric Run Jan 29 '16 at 22:54 — run Eric Run, Jan 29 '16 at 22:54

add a column in MySQL rank by deal by day

single day with title, price

1 Answers1