CODEWITHSUNDEEP
mysql
floss
floss

Reputation: 2773

How to select DATE related to MAX value column in MySQL

I have a table tbl with column date sales

I perform this MySQL query:

SELECT DAY(date) as DAY, count(sales) as sales_count
FROM tbl

I get this result:

DAY      sales_count
1        224
2        361
3        155
4        281
.......
30       416
31       332

Now, I find the DAY in WEEK when the sales were MAX by doing this:

SELECT DAY(date) as DAY, WEEK(date) as WEEK, max(count(sales)) as MAX_SALES, 
FROM tbl
GROUP BY WEEK(date)

and I get this:

DAY    WEEK      MAX_SALES
1      1         324
8      2         461
15     3         255
22     4         481
.......

The WEEK and MAX_SALES are correct but the DAY value seems to be the first DAY of the WEEK and not the DAY of the MAX_SALES occurred. I can confirm it is because we have GROUP BY WEEK and that is how DAY is getting the value related to WEEK. We cannot group by MAX()

How do I have the DAY value reflect the MAX_SALES date in the WEEK?

Upvotes: 0

Views: 39

Answers (1)

D-Shih
D-Shih

Reputation: 46219

You can try to use subquery.

SELECT weekDay,
       DAY,
       MAX(sales_count)
FROM (
    SELECT DAY(date) as DAY, 
           Week(date) as weekDay
           count(sales) as sales_count
    FROM tbl
    GROUP BY 
        DAY(date),
        Week(date)      
) t1
group by 
       weekDay,
       DAY

Upvotes: 1

Related Questions