0

I am trying to retrieve last 3 months records. I need to sum order total amount by week. I have made following query.

select CONCAT("Week", WEEK(created_at)) as week_number, sum(total_cost) 
from certified_diamond_orders 
where created_at > 2016-11-22 
and status = "delivered" 
group by week("created_at")

But I am only getting one record with this. Infact my table has 2 years entries. Also I was trying to figure out how I can pull week start date and end date to diplay on my chart.

Any suggestions where I am making mistake?

SIDU
  • 2,258
  • 1
  • 12
  • 23
Jass
  • 3,345
  • 3
  • 24
  • 41
  • http://stackoverflow.com/questions/3317980/getting-first-day-of-the-week-in-mysql-using-week-no – SIDU Feb 22 '17 at 05:02

1 Answers1

2

week("created_at") looks like you're trying to determine the week of the string "created_at" rather than the column created_at. Which might explain why you're only getting one row in your result.

The date 2016-11-22 also looks suspiciously like a sum instead of a date (2016 - 11 - 22 = 1983 vs "2016-11-22"

Try this:

SELECT
  CONCAT('Week', WEEK(created_at)) AS week_number,
  SUM(total_cost)
FROM certified_diamond_orders 
WHERE
  created_at > '2016-11-22' AND
  status = 'delivered'
GROUP BY WEEK(created_at)
aidan
  • 9,310
  • 8
  • 68
  • 82
  • It worked. any idea how I can bring week start and end date in result? – Jass Feb 22 '17 at 04:18
  • Maybe something like this: `DATE_ADD(created_at, INTERVAL(1-DAYOFWEEK(created_at)) DAY)` AS start_of_week, DATE_ADD(created_at, INTERVAL(7-DAYOFWEEK(created_at)) DAY) AS end_of_week`, depends on what day your weeks starts: Sunday or Monday. Have a look here: https://stackoverflow.com/questions/6944055/how-do-i-get-the-first-day-of-the-week-of-a-date-in-mysql – aidan Feb 22 '17 at 07:06