471,092 Members | 1,566 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,092 software developers and data experts.

Select with dummy rows when grouping?

If I select from a table and, for example, group by the month or day
to determine how much activity there is day to day or month to month,
it will only return months and days with a record and leave out any
month or day without any records making it apear at a glance that
every day or month has activity which forces me or whoever to have to
look at the date column and actively look for spots where a day or
more elapses without a single row in order to determine when nothing
has happened. Is there any select method for grouping on something
like a month and to have it output a dummy row for missing days or
months without having to write a program that makes an individual
select for each day?

Thanks!
Jul 19 '05 #1
2 5020
"JohnnyOnTheSpot" <an************@hotmail.com> wrote in message
news:e8**************************@posting.google.c om...
If I select from a table and, for example, group by the month or day
to determine how much activity there is day to day or month to month,
it will only return months and days with a record and leave out any
month or day without any records making it apear at a glance that
every day or month has activity which forces me or whoever to have to
look at the date column and actively look for spots where a day or
more elapses without a single row in order to determine when nothing
has happened. Is there any select method for grouping on something
like a month and to have it output a dummy row for missing days or
months without having to write a program that makes an individual
select for each day?

Thanks!


To my knowledge, MySQL cannot return records that don't exist, nor can it
predict sequences and generate output. (If I'm wrong, I'm sure someone here
will quickly correct me, wouldn't be the first time :-) Can you enter a
dummy record into the table for days with no activity? That is the only way
I can think of to do what you want. If you were to start each day by
entering an empty record for that day, then you would get at least one hit
for each day in a weekly or monthly query, thus solving your problem.

JM
Jul 19 '05 #2
"JohnnyOnTheSpot" <an************@hotmail.com> wrote in message
news:e8**************************@posting.google.c om...
If I select from a table and, for example, group by the month or day
to determine how much activity there is day to day or month to month,
it will only return months and days with a record and leave out any
month or day without any records making it apear at a glance that
every day or month has activity which forces me or whoever to have to
look at the date column and actively look for spots where a day or
more elapses without a single row in order to determine when nothing
has happened. Is there any select method for grouping on something
like a month and to have it output a dummy row for missing days or
months without having to write a program that makes an individual
select for each day?

Thanks!


To my knowledge, MySQL cannot return records that don't exist, nor can it
predict sequences and generate output. (If I'm wrong, I'm sure someone here
will quickly correct me, wouldn't be the first time :-) Can you enter a
dummy record into the table for days with no activity? That is the only way
I can think of to do what you want. If you were to start each day by
entering an empty record for that day, then you would get at least one hit
for each day in a weekly or monthly query, thus solving your problem.

JM
Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by JohnnyOnTheSpot | last post: by
4 posts views Thread by Mick White | last post: by
4 posts views Thread by Kris Rudin | last post: by
2 posts views Thread by Flinky Wisty Pomm | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.