Hi, this is quite a basic question I think, but I'm still learning

If I have a table like this:

Date Amount
2007-10-01 10.39
2007-10-03 2.15
2007-10-03 4.92
2007-10-04 6.00
2007-10-06 1.23
2007-10-06 13.67

What query would I have to run to get this output?:

Date Total amount
2007-10-01 10.39
2007-10-02 0
2007-10-03 7.07
2007-10-04 6.00
2007-10-05 0
2007-10-06 14.9
2007-10-07 0

I'm having problems getting a line in the output for a date that
doesn't exist in the table. Is it possible to do this?

Thanks.

On 3 Oct, 13:33, Mike Harrison <mjh...@yahoo.co.uk> wrote:
> Hi, this is quite a basic question I think, but I'm still learning
>
> If I have a table like this:
>
> Date Amount
> 2007-10-01 10.39
> 2007-10-03 2.15
> 2007-10-03 4.92
> 2007-10-04 6.00
> 2007-10-06 1.23
> 2007-10-06 13.67
>
> What query would I have to run to get this output?:
>
> Date Total amount
> 2007-10-01 10.39
> 2007-10-02 0
> 2007-10-03 7.07
> 2007-10-04 6.00
> 2007-10-05 0
> 2007-10-06 14.9
> 2007-10-07 0
>
> I'm having problems getting a line in the output for a date that
> doesn't exist in the table. Is it possible to do this?
>
> Thanks.

The usual way is to join with a calendar table containing all dates.

On Oct 3, 2:46 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 3 Oct, 13:33, Mike Harrison <mjh...@yahoo.co.uk> wrote:
>
>
>
>
>
>
>
> > Hi, this is quite a basic question I think, but I'm still learning
>
> > If I have a table like this:
>
> > Date Amount
> > 2007-10-01 10.39
> > 2007-10-03 2.15
> > 2007-10-03 4.92
> > 2007-10-04 6.00
> > 2007-10-06 1.23
> > 2007-10-06 13.67
>
> > What query would I have to run to get this output?:
>
> > Date Total amount
> > 2007-10-01 10.39
> > 2007-10-02 0
> > 2007-10-03 7.07
> > 2007-10-04 6.00
> > 2007-10-05 0
> > 2007-10-06 14.9
> > 2007-10-07 0
>
> > I'm having problems getting a line in the output for a date that
> > doesn't exist in the table. Is it possible to do this?
>
> > Thanks.
>
> The usual way is to join with a calendar table containing all dates.

Or -if possible- leave it up to e.g. PHP. E.g. have an array compared
to the output.

On Wed, 03 Oct 2007 05:33:45 -0700, Mike Harrison wrote:
> Hi, this is quite a basic question I think, but I'm still learning
>
> If I have a table like this:
>
> Date Amount
> 2007-10-01 10.39
> 2007-10-03 2.15
> 2007-10-03 4.92
> 2007-10-04 6.00
> 2007-10-06 1.23
> 2007-10-06 13.67
>
> What query would I have to run to get this output?:
>
> Date Total amount
> 2007-10-01 10.39
> 2007-10-02 0
> 2007-10-03 7.07
> 2007-10-04 6.00
> 2007-10-05 0
> 2007-10-06 14.9
> 2007-10-07 0
>
> I'm having problems getting a line in the output for a date that
> doesn't exist in the table. Is it possible to do this?

Sure. Make a table with all the dates you want to list in it. Join
your real table to it with an outer join.

--
18. I will not have a son. Although his laughably under-planned attempt to
usurp power would easily fail, it would provide a fatal distraction at a
crucial point in time.
--Peter Anspach's list of things to do as an Evil Overlord

On Oct 3, 5:05 pm, "Peter H. Coffin" <hell...@ninehells.com> wrote:

> Sure. Make a table with all the dates you want to list in it. Join
> your real table to it with an outer join.
>

Thanks for the responses. So what's the easiest way to create a table
of dates? Is there a way to automate it or have I to enter it all
manually?

For example, I would like a table of dates from 2007-09-01 to
2008-12-31.

I guess one way would be to create the column of dates in Excel or
another spreadsheet, export to a file and then LOAD DATA INFILE. But
is there an easier way to do it?

On Wed, 03 Oct 2007 10:37:38 -0700, Mike Harrison wrote:
> On Oct 3, 5:05 pm, "Peter H. Coffin" <hell...@ninehells.com> wrote:
>
>> Sure. Make a table with all the dates you want to list in it. Join
>> your real table to it with an outer join.
>>
>
> Thanks for the responses. So what's the easiest way to create a table
> of dates? Is there a way to automate it or have I to enter it all
> manually?
>
> For example, I would like a table of dates from 2007-09-01 to
> 2008-12-31.
>
> I guess one way would be to create the column of dates in Excel or
> another spreadsheet, export to a file and then LOAD DATA INFILE. But
> is there an easier way to do it?

Any number of application programs would do it simply, with loop over an
INSERT query. It's just not something that SQL is well-suited for. It's
something you only have to do ONCE, after all, if you make a big enough
table. For anything else, you just do something like

INSERT INTO my_dates (my_date)
SELECT date_col
FROM master_dates
WHERE date_col >= DATE('2007-09-01') AND
date_col <= DATE('2008-12-31');

Your master date table can run for 100 years with less than 36,000 rows,
after all.

--
For every subject you can think of there are at least 3 web sites.
The owners of these web sites know each other and at least one of
them hates at least one of the others.
-- mnlooney's view of Skif's Internet Theorem