CODEWITHSUNDEEP
phpmysqldategroup-by
Jared
Jared

Reputation: 301

Count number of records per week giving massive number of NULL

I have tried about 10 different variations of queries similar to the following:

SELECT WEEKOFYEAR( dateline ) AS weekno, COUNT( 1 ) AS posts
FROM post
GROUP BY WEEKOFYEAR( dateline )
LIMIT 0 , 30

The resulting data is always a few results and about 600,000 "NULL" like so:

weekno  posts
NULL    591843
1   57
2   42
3   25
4   44
5   9
6   38
7   15
8   41
9   10
10  130
11  77
12  69
13  36
14  25
15  25
16  24
17  44
18  42
19  14
20  49
21  70
22  41
23  40
24  57
25  40
26  31

Here is the table structure for dateline:

#   Name    Type    Collation   Attributes  Null    Default     Extra
7   dateline    int(10)         UNSIGNED    No  0

All of the rows have a value in dateline.

I need to get some sort of group by week working because ultimately I want to graph out the post activity with and without a specified forum section. I don't know why I'm getting all of these NULL results.

Upvotes: 0

Views: 122

Answers (1)

rs.
rs.

Reputation: 27427

I think your date is stored in unixtime integer format, assuming that it is unixtime try FROM_UNIXTIME function

SELECT WEEKOFYEAR(FROM_UNIXTIME(dateline)) AS weekno, COUNT( 1 ) AS posts
FROM post
GROUP BY WEEKOFYEAR(FROM_UNIXTIME(dateline))
LIMIT 0 , 30

To return weeks from different year try this

SELECT 
FROM_UNIXTIME(dateline, '%Y') Year,
WEEKOFYEAR(FROM_UNIXTIME(dateline)) AS weekno, COUNT( 1 ) AS posts
FROM post
GROUP BY FROM_UNIXTIME(dateline, '%Y'), WEEKOFYEAR(FROM_UNIXTIME(dateline))
LIMIT 0 , 30

DEMO HERE

Upvotes: 1

Related Questions