0

I have a mysql table that has columns for 'created_at' and 'type', among others. What I am trying to do is get the total number of rows created each week, and further have that number broken down by 'type'.

That is, if my data is as follows:

created_at   |  type
10/15/2015      car
10/15/2015      bike
10/16/2015      car
10/24/2015      car
10/24/2015      car
10/25/2015      car
10/26/2015      bike

I want my query to return something like:

Week of    | Total  |  car  |  bike
10/15/2015     3      2       1
10/22/2015     4      3       1

From this stackoverflow question (How to group by week in MySQL?), I am able to group by week, but I am having difficulty further breaking down the results by 'type'. The closest I have gotten is:

SELECT
  FROM_DAYS(TO_DAYS(created_at) -MOD(TO_DAYS(created_at) -1, 7)) AS week_of,
  type,
  COUNT(*) AS 'rows'
FROM mytable
GROUP BY FROM_DAYS(TO_DAYS(created_at) -MOD(TO_DAYS(created_at) -1, 7)), type
ORDER BY FROM_DAYS(TO_DAYS(created_at) -MOD(TO_DAYS(created_at) -1, 7));

Which is a bit different then what I was trying to get, but its something I can still work with. However, it doesn't include the total per week (would prefer having all the data I want returned in the result set). How would I go about doing this?

Since I don't know the values in 'type', I don't want to specify those in the query directly, especially since those can be changed over time.

Community
  • 1
  • 1
Hawkeye001
  • 791
  • 2
  • 11
  • 25

1 Answers1

0

Use group by and week 

select week(created_at), type, count(*) 
from my_table 
where year(created_at) = year(curdate())
group by week(created_at), type
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • Using week in my scenario doesn't work since my data can span years, and WEEK() just appears to return the week number in the year. This is similar to what I tried, but doesn't have the summed up type count. – Hawkeye001 Apr 26 '16 at 20:24
  • I have update the answer – ScaisEdge Apr 26 '16 at 20:26