daily reports

diego

unread,

Apr 23, 2013, 9:22:23 AM4/23/13

to

Hello there!!
im rebuilding google analytics (nahhh kidding..)

i have this table

clicks
- id
- date
- ip

i want to generate monthly reports

SELECT count(id)
FROM clicks
GROUP BY MONTH(date), YEAR(date)
ORDER BY date ASC

and daily..

SELECT count(id)
FROM clicks
GROUP BY MONTH(date), YEAR(date), DAY(date)
ORDER BY date ASC

the problem with the last query is... i have 0 clicks on a certain day.
the query will return a gap between dates. how can i fill that with 0?

i found on google that creating a calendar table will "fix" that. but i need to generate
a lot of rows. maybe there is another solution to this problem.

thanks!!

diego

Salvatore

unread,

Apr 23, 2013, 10:03:44 AM4/23/13

to

On 2013-04-23, diego <dieg...@gmail.com> wrote:
> Hello there!!
> im rebuilding google analytics (nahhh kidding..)
>
> i have this table
>
> clicks
> - id
> - date
> - ip
>

> [snip]

>
> the problem with the last query is... i have 0 clicks on a certain day.
> the query will return a gap between dates. how can i fill that with 0?

I can't think a simple fix offhand, but I can tell you that, if you
happen to create another table like this:

datetable
- date DATE NOT NULL
- (more relevant columns here, if any)

...and if you fill it with dates, then perform a LEFT JOIN on the
"clicks" table like so:

SELECT `d`.`date` AS `date`, COUNT(c.ip) AS ip_count
FROM datetable d
LEFT JOIN clicks c ON `d`.`date` = `c`.`date`
GROUP BY `date`

...you'll get rows with dates and zeros instead of not getting any rows
at all.

--
Blah blah bleh...
GCS/CM d(-)@>-- s+:- !a C++$ UBL++++$ L+$ W+++$ w M++ Y++ b++

Luuk

unread,

Apr 24, 2013, 2:55:23 PM4/24/13

to

On 23-04-2013 15:22, diego wrote:
> SELECT count(id)
> FROM clicks
> GROUP BY MONTH(date), YEAR(date), DAY(date)
> ORDER BY date ASC

why do you group by this 3 things,
and not a simple 'GROUP BY date' ??

diego

unread,

Apr 24, 2013, 11:06:48 PM4/24/13

to

yeap. i should do that too :)

diego

unread,

Apr 24, 2013, 11:17:24 PM4/24/13

to

oh.. i remember why i did it that way
was because i want to generate reports by day or by month
i tried using GROUP BY DATE_FORMAT(date,'%Y-%m-%d') with no success :)

On Tuesday, April 23, 2013 10:22:23 AM UTC-3, diego wrote:

Thomas 'PointedEars' Lahn

unread,

Apr 25, 2013, 9:14:51 AM4/25/13

to

diego wrote:

> yeap. i should do that too :)

Yeap? Do *what*? And please do not top-post either.

--
PointedEars

Twitter: @PointedEars2
Please do not Cc: me. / Bitte keine Kopien per E-Mail.

Jürgen Exner

unread,

Apr 25, 2013, 10:51:20 AM4/25/13

to

On Thu, 25 Apr 2013 15:14:51 +0200, Thomas 'PointedEars' Lahn
<Point...@web.de> wrote in comp.databases.mysql:

>diego wrote:
>
>> yeap. i should do that too :)
>
>Yeap? Do *what*? And please do not top-post either.

And also please do not add emtpy lines after each and every line that
you quote. That makes it rather irritating to read the quoted text.

jue

Thomas 'PointedEars' Lahn

unread,

Apr 25, 2013, 11:01:46 AM4/25/13

to

J�rgen Exner wrote:
^

> […] Thomas 'PointedEars' Lahn […] wrote […]:

>> diego wrote:
>>> yeap. i should do that too :)
>>
>> Yeap? Do *what*? And please do not top-post either.
>
> And also please do not add emtpy lines after each and every line that
> you quote. That makes it rather irritating to read the quoted text.

That is yet another bug of the buggy Google Groups Web interface that the OP
is using to post to Usenet.

As for posting style, it's attribution _line_, not novel. And you should
not be using Forté Agent if you have non-ASCII characters in your name :)
They are not properly (MIME-)encoded. Try Mozilla Thunderbird or KNode
instead.

F'up2 poster

diego

unread,

Apr 25, 2013, 4:53:46 PM4/25/13

to

what is your problem?

Thomas 'PointedEars' Lahn

unread,

Apr 25, 2013, 4:56:07 PM4/25/13

to

diego wrote:

> what is your problem?
>
> [Top-post with attribution novel and added empty lines again]

You are not ready for the answer yet.

J�rgen Exner

unread,

Apr 25, 2013, 8:21:14 PM4/25/13

to

diego <dieg...@gmail.com> wrote:

>what is your problem?

Let's see. Right here it's top-posting.
Then below full-quoting

>
>On Thursday, April 25, 2013 10:14:51 AM UTC-3, Thomas 'PointedEars' Lahn wrote:
>> diego wrote:
>>
>>
>>

Here it's three useless empty lines which even worse you attributed to
Thomas but which he never wrote. One line may be fine for structuring
your post, but three?

>> > yeap. i should do that too :)
>>
>>
>>

Here it's three more useless empty lines which even worse you attributed
to Thomas but which he never wrote. One line may be fine for structuring
your post, but three?

>> Yeap? Do *what*? And please do not top-post either.
>>
>>
>>

Here it's three useless empty lines which even worse you attributed to
Thomas but which he never wrote. One line may be fine for structuring
your post, but three?

>> --

Is there a specific reason why you are quoting the signature?

>>

And another empty line wrongly attributed to Thomas

>> PointedEars
>>
>>
>>

Here it's three useless empty lines which even worse you attributed to
Thomas but which he never wrote. One line may be fine for structuring
your post, but three?

>> Twitter: @PointedEars2
>>

And another empty line wrongly attributed to Thomas

>> Please do not Cc: me. / Bitte keine Kopien per E-Mail.

Those are just the problems I saw on a first glimps, maybe there are
more.

jue

diego

unread,

Apr 25, 2013, 8:36:24 PM4/25/13

to

Sorry guys im using Google groups for this
I reply te messages like a regular email