CODEWITHSUNDEEP
mysql
Jass
Jass

Reputation: 4103

How to sum order total amount by week

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?

Upvotes: 0

Views: 141

Answers (1)

aidan
aidan

Reputation: 9576

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)

Upvotes: 2

Related Questions