MySQL Select all days in a month

#1

I have a table that I am pulling results from, but there are missing
days.

i.e. Select id, date, count from table:

id date count

1 2/1/2010 10
2 2/3/2010 5
3 2/4/2010 4
4 2/5/2010 2

As you can see in my simplified example I do not have any data for
2/2/2010

What I'd like to do is join this query to something that selects all
the days for a date range so that I can make sure I account for every
day in a given month.

I'd like my data set to look like this:

id date count

1 2/1/2010 10
2/2/2010 0
2 2/3/2010 5
3 2/4/2010 4
4 2/5/2010 2

I know how to pull all dates in a month or date range within oracle,
but cannot figure out a way with mysql.. any ideas?

#2

On Feb 5, 1:00*pm, master44 <trp... (AT) gmail (DOT) com> wrote:

Quote:

I have a table that I am pulling results from, but there are missing
days.

i.e. Select id, date, count from table:

id * * date * * * * count

1 * * 2/1/2010 * * 10
2 * * 2/3/2010 * * *5
3 * * 2/4/2010 * * *4
4 * * 2/5/2010 * * *2

As you can see in my simplified example I do not have any data for
2/2/2010

What I'd like to do is join this query to something that selects all
the days for a date range so that I can make sure I account for every
day in a given month.

I'd like my data set to look like this:

id * * date * * * * count

1 * * 2/1/2010 * * 10
* * * *2/2/2010 * * *0
2 * * 2/3/2010 * * *5
3 * * 2/4/2010 * * *4
4 * * 2/5/2010 * * *2

I know how to pull all dates in a month or date range within oracle,
but cannot figure out a way with mysql.. any ideas?

Use a LEFT (outer) JOIN against a table of day numbers 1..31. See
also: LAST_DAY().

#3

On 5 Feb, 19:00, master44 <trp... (AT) gmail (DOT) com> wrote:

Quote:

I have a table that I am pulling results from, but there are missing
days.

i.e. Select id, date, count from table:

id * * date * * * * count

1 * * 2/1/2010 * * 10
2 * * 2/3/2010 * * *5
3 * * 2/4/2010 * * *4
4 * * 2/5/2010 * * *2

As you can see in my simplified example I do not have any data for
2/2/2010

What I'd like to do is join this query to something that selects all
the days for a date range so that I can make sure I account for every
day in a given month.

I'd like my data set to look like this:

id * * date * * * * count

1 * * 2/1/2010 * * 10
* * * *2/2/2010 * * *0
2 * * 2/3/2010 * * *5
3 * * 2/4/2010 * * *4
4 * * 2/5/2010 * * *2

I know how to pull all dates in a month or date range within oracle,
but cannot figure out a way with mysql.. any ideas?

One common way is to generate a calendar table and either use an outer
join or a union against that.

/Lennart

#4

Lennart:

Quote:

On 5 Feb, 19:00, master44 <trp... (AT) gmail (DOT) com> wrote:

What I'd like to do is join this query to something that selects
all the days for a date range so that I can make sure I account
for every day in a given month.

One common way is to generate a calendar table and ...

This is such a common situation (a true FAQ), that I would expect a
database management system to have such table 'on board'. Isn't it
amazing, that merely every application in this World (using MySQL) has
to generate its own calendar table?

Am I the only one who thinks this is a shortcoming of MySQL (if such
table is indeed not built-in)?

--
Erick

#5

Erick T. Barkhuis wrote:

Quote:

Lennart:

On 5 Feb, 19:00, master44 <trp... (AT) gmail (DOT) com> wrote:

What I'd like to do is join this query to something that selects
all the days for a date range so that I can make sure I account
for every day in a given month.

One common way is to generate a calendar table and ...

This is such a common situation (a true FAQ), that I would expect a
database management system to have such table 'on board'. Isn't it
amazing, that merely every application in this World (using MySQL) has
to generate its own calendar table?

Am I the only one who thinks this is a shortcoming of MySQL (if such
table is indeed not built-in)?

Submit a patch and motivate why it should be added and if they think it's
worth to do then they add it.

--

//Aho

#6

Erick T. Barkhuis wrote:

Quote:

Lennart:

On 5 Feb, 19:00, master44 <trp... (AT) gmail (DOT) com> wrote:

What I'd like to do is join this query to something that selects
all the days for a date range so that I can make sure I account
for every day in a given month.

One common way is to generate a calendar table and ...

This is such a common situation (a true FAQ), that I would expect a
database management system to have such table 'on board'. Isn't it
amazing, that merely every application in this World (using MySQL) has
to generate its own calendar table?

Am I the only one who thinks this is a shortcoming of MySQL (if such
table is indeed not built-in)?

I've got about 50 tables and I've never needed that one.

I'd far rather see a much more common situation addressed: namely many
to one correspondences implicitly connecting table fields and records.

So that when e.g. I find the same customer on the database in four
separate instances, I could easily combine them and all his orders would
magically now attach to the same person.

#7

The Natural Philosopher:

Quote:

Erick T. Barkhuis wrote:
Lennart:

On 5 Feb, 19:00, master44 <trp... (AT) gmail (DOT) com> wrote:

What I'd like to do is join this query to something that selects
all the days for a date range so that I can make sure I account
for every day in a given month.

One common way is to generate a calendar table and ...

This is such a common situation (a true FAQ), that I would expect a
database management system to have such table 'on board'. Isn't it
amazing, that merely every application in this World (using MySQL)
has to generate its own calendar table?

Am I the only one who thinks this is a shortcoming of MySQL (if such
table is indeed not built-in)?

I've got about 50 tables and I've never needed that one.

So, how do you show the number of orders for each calender day in the
system described below?

Quote:

I'd far rather see a much more common situation addressed: namely
many to one correspondences implicitly connecting table fields and
records.

You mean: foreign keys? Doesn't InnoDB address this?

Quote:

So that when e.g. I find the same customer on the database in four
separate instances, I could easily combine them and all his orders
would magically now attach to the same person.

I'm totally lost...how do you recognize "the same customer in four
instances" when not by unique ID? And if that's how you recognize them,
how can there be more than one of the same customer in the customer
table at all?
Sounds like you simply haven't normalized your database, or you allow
entry of "the same customer" multiple times in your application (which
would automatically mean, that two same curstomers are in fact separate
customers in that system).

--
Erick

#8

Op 6-2-2010 14:20, Erick T. Barkhuis schreef:

Quote:

The Natural Philosopher:

Erick T. Barkhuis wrote:
Lennart:

On 5 Feb, 19:00, master44 <trp... (AT) gmail (DOT) com> wrote:

What I'd like to do is join this query to something that selects
all the days for a date range so that I can make sure I account
for every day in a given month.

One common way is to generate a calendar table and ...

This is such a common situation (a true FAQ), that I would expect a
database management system to have such table 'on board'. Isn't it
amazing, that merely every application in this World (using MySQL)
has to generate its own calendar table?

Am I the only one who thinks this is a shortcoming of MySQL (if such
table is indeed not built-in)?

I've got about 50 tables and I've never needed that one.

So, how do you show the number of orders for each calender day in the
system described below?

I'd far rather see a much more common situation addressed: namely
many to one correspondences implicitly connecting table fields and
records.

You mean: foreign keys? Doesn't InnoDB address this?

So that when e.g. I find the same customer on the database in four
separate instances, I could easily combine them and all his orders
would magically now attach to the same person.

I'm totally lost...how do you recognize "the same customer in four
instances" when not by unique ID? And if that's how you recognize them,
how can there be more than one of the same customer in the customer
table at all?
Sounds like you simply haven't normalized your database, or you allow
entry of "the same customer" multiple times in your application (which
would automatically mean, that two same curstomers are in fact separate
customers in that system).

but customers can merge into one new customer

speyker + saab ==> speyker

there where 2, now there's only 1....

--
Luuk

#9

Luuk:

Quote:

Op 6-2-2010 14:20, Erick T. Barkhuis schreef:

Sounds like you simply haven't normalized your database, or you
allow entry of "the same customer" multiple times in your
application (which would automatically mean, that two same
curstomers are in fact separate customers in that system).

but customers can merge into one new customer
speyker + saab ==> speyker

there where 2, now there's only 1....

Sounds like three different customers to me.
- Spijker (customer ID 12345)
- Saab (customer ID 32887)
- Spijker (customer ID 54321)
All you want to be able to is to transfer open orders from one customer
to another. But that's something that must be implemented in the
application anyway, in order to be able to correct data entry errors.

--
Erick

#10

Luuk wrote:

Quote:

but customers can merge into one new customer

speyker + saab ==> speyker

there where 2, now there's only 1....

If it were my database, I would not change the company ID in any
transactions that took place before the purchase/merger. In fact, I
would leave Saab in the company database (perhaps marked inactive so no
new transactions can occur), but history and auditing should demand that
Saab-only records remained unchanged and do not disappear.

--
-bts
-Four wheels carry the body; two wheels move the soul