Group by week and select records from current week

Question

I have been trying to display a table of results for weekly sales using MySQL, where the results of the week should be like:

Sun - Mon - Tues - Wed - Thur - Fri - Sat

So I went ahead and used this query:

SELECT DATE_FORMAT(cart_calendar.datefield, '%d-%b') AS DATE
        , IFNULL(SUM(cart_daily_sales.quantity),0) AS total_sales 
FROM cart_daily_sales 
RIGHT JOIN cart_calendar 
         ON (DATE(cart_daily_sales.order_date) = cart_calendar.datefield)
WHERE (cart_calendar.datefield) 
GROUP BY WEEK(cart_calendar.datefield)

I know I'm almost there as this outputs all of the 52 weeks in the year, I only want the current week result unlike the above code that results to this...

'02-Jan', '09-Jan', '16-Jan', '23-Jan', '30-Jan', '06-Feb', '13-Feb', '20-Feb', '27-Feb', '06-Mar' e.t.c....

I know I can use the BETWEEN operator for today's date and then -7 days but this doesn't show the current week, if today's day is Wednesday it will show the last seven days to last weeks Wednesday, not the correct week structure.

How do I display last week's sales grouped per week?

Answer 1

 SELECT DATE_FORMAT(cc.datefield, '%d-%b') AS DATE
        , SUM(IFNULL(cds.quantity,0)) AS total_sales 
 FROM cart_calendar cc
 INNER JOIN cart_daily_sales cds
         ON (DATE(cds.order_date) = cc.datefield)
 WHERE WEEK(cc.datefield) = WEEK(now())
 GROUP BY WEEK(cc.datefield)

Or if you want it per day:

 SELECT DATE_FORMAT(cc.datefield, '%d-%b') AS DATE
        , SUM(IFNULL(cds.quantity,0)) AS total_sales 
 FROM cart_calendar cc
 INNER JOIN cart_daily_sales cds
         ON (DATE(cds.order_date) = cc.datefield)
 WHERE WEEK(cc.datefield) = WEEK(now())
 GROUP BY MOD(WEEKDAY(cc.datefield)+1,6) WITH ROLLUP