0

I have a bunch of data in several tables. Each row has a ts column that is a data type TIMESTAMP, looks like this:

2013-11-20 11:20:14

I'd like to produce counts for each week of the year to plot a graph. So the results I'd like are like this:

week1 5
week2 16
week3 19
week4 27

For example. Is it possible to do this with SQL? I mean I could manually do it with a PHP script but I feel like there must be a way to use the COUNT() function in SQL to do this.

Chud37
  • 4,907
  • 13
  • 64
  • 116
  • hi have a look at http://stackoverflow.com/questions/17198468/from-the-timestamp-in-sql-selecting-records-from-today-yesterday-this-week-t – Marius Brits May 08 '17 at 07:15

3 Answers3

3

Without knowing to much about the specifics about the table structure. I think you can do this:

SELECT
    WEEK(timestamp),
    COUNT(*) as nbr
FROM
    table
GROUP BY 
    WEEK(timestamp)

But I think it would be good to also consider year in the calculation. Like this:

SELECT
    YEAR(timestamp),
    WEEK(timestamp),
    COUNT(*) as nbr
FROM
    table
GROUP BY 
    YEAR(timestamp),
    WEEK(timestamp)

I would also think that the "week" part you can append later in your php code.

Edit

It is true what Juergen d said in the comment. You could also use yearweek(). Like this:

SELECT
    yearweek(timestamp),
    COUNT(*) as nbr
FROM
    table
GROUP BY 
    yearweek(timestamp)

References:

Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
Arion
  • 31,011
  • 10
  • 70
  • 88
  • Wow, I've never heard of those functions! I knew there must have been an easy way to do it. Thank you! – Chud37 May 08 '17 at 07:18
  • I didn't downvote you, but for some reason the data I'm getting (the COUNT column) isnt incrementing: `// 201346 59 // 201347 44 // 201348 21 // 201349 15 // 201350 10` – Chud37 May 08 '17 at 07:24
  • 1
    I don't understand what you mean about "incrementing"? – Arion May 08 '17 at 07:26
  • Sorry. I realise the COUNT() is counting the entries in that week only. I need it to include all the counts *up to* that week, so each week is the total entries from the start up to that week. I hope that makes sense :P – Chud37 May 08 '17 at 07:28
1

You can use WEEK function in mysql

EX:

Select WEEK(<timesatmp>),count(*) from Table group by WEEK (<timesatmp>)
Mahesh Madushanka
  • 2,902
  • 2
  • 14
  • 28
0

You could use week function to get the week number:

select week(ts),
    count(*)
from your_table
group by week(ts);

or year and week together like 201719 using yearweek:

select yearweek(ts),
    count(*)
from your_table
group by yearweek(ts);
Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76