Include date from week number in the following query

Question

I have the following SQL query that returns the top five weeks where the most questions were answered. The weeks are being returned by the number but I want the first date of the week instead of the number.

SELECT Count(*) AS answered, 
   Week(score_datetime) AS week, 
   Year(score_datetime) AS year 
FROM   development.score 
WHERE  score_datetime IS NOT NULL 
GROUP  BY Week(score_datetime) 
ORDER  BY answered DESC 
LIMIT  5;

My sql skills aren't the best so I am currently getting the date from the week number when returned with momentjs. So if anyone knows how I could include the date in the above query by what the week number and year is it would be much appreciated.

If you select a (non-aggregated) column then in general you need to group by it too. So, YEAR(score_datetime) in this instance. — Strawberry, Feb 12 '16 at 15:33

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

The answer to your specific question can be found at: How to convert number of week into date?

So, based on the answer the conversion formula is: WEEKDAY(DATE_ADD(MAKEDATE(year, 1), INTERVAL Week(score_datetime) WEEK))

Putting it together:

  SELECT Count(*) AS answered, 
DATE_ADD(MAKEDATE(Year(score_datetime), 1), INTERVAL Week(score_datetime) WEEK) AS week, 
       Year(score_datetime) AS year 
    FROM   development.score 
    WHERE  score_datetime IS NOT NULL 
    GROUP  BY Week(score_datetime) 
    ORDER  BY answered DESC 
    LIMIT  5;

score 1 · Accepted Answer · answered Feb 12 '16 at 15:25

Try something like this:

SELECT Count(*) AS answered, 
   Week(score_datetime) AS week,
   DATE_ADD(score_datetime, INTERVAL(1-DAYOFWEEK(score_datetime)) DAY) as week_start,
   Year(score_datetime) AS year 
FROM   development.score 
WHERE  score_datetime IS NOT NULL 
GROUP  BY Week(score_datetime) 
ORDER  BY answered DESC 
LIMIT  5;

Include date from week number in the following query

2 Answers2