I have a table called my_expenses that has the following fields:


id int AUTO_INCREMENT PRIMARY KEY,
date date not null,
item varchar(255) not null,
amount decimal (4, 2) not null

I need to know the total amount of daily expenses I incur each day
with a grand total of the totals column. So, I want my output as such:

----------------------------------
date total
----------------------------------
2007-10-12 20.00
2007-10-13 0.00
2007-10-14 3.62
2007-10-15 6.48
------------------------------------
Grand total 30.10
------------------------------------

I've been able to get the sum for each day but I don't know how to get
the grand total. Here's what I've got so far:

select date, sum(amount) as TOTAL from my_expenses group by date;

That gives me something like this:

----------------------------------
date total
----------------------------------
2007-10-12 20.00
2007-10-13 0.00
2007-10-14 3.62
2007-10-15 6.48


Now I want the grand total as well. Please .

On 15 Oct, 16:50, Water Cooler v2 <wtr_...@yahoo.com> wrote:
> I have a table called my_expenses that has the following fields:
>
> id int AUTO_INCREMENT PRIMARY KEY,
> date date not null,
> item varchar(255) not null,
> amount decimal (4, 2) not null
>
> I need to know the total amount of daily expenses I incur each day
> with a grand total of the totals column. So, I want my output as such:
>
> ----------------------------------
> date total
> ----------------------------------
> 2007-10-12 20.00
> 2007-10-13 0.00
> 2007-10-14 3.62
> 2007-10-15 6.48
> ------------------------------------
> Grand total 30.10
> ------------------------------------
>
> I've been able to get the sum for each day but I don't know how to get
> the grand total. Here's what I've got so far:
>
> select date, sum(amount) as TOTAL from my_expenses group by date;
>
> That gives me something like this:
>
> ----------------------------------
> date total
> ----------------------------------
> 2007-10-12 20.00
> 2007-10-13 0.00
> 2007-10-14 3.62
> 2007-10-15 6.48
>
> Now I want the grand total as well. Please .

This sort of thing is supplied by a query management facility, not by
the query itself.

You need to surround the query with some other application, or write
it yourself.

On Oct 15, 5:02 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> On 15 Oct, 16:50, Water Cooler v2 <wtr_...@yahoo.com> wrote:
>
>
>
>
>
> > I have a table called my_expenses that has the following fields:
>
> > id int AUTO_INCREMENT PRIMARY KEY,
> > date date not null,
> > item varchar(255) not null,
> > amount decimal (4, 2) not null
>
> > I need to know the total amount of daily expenses I incur each day
> > with a grand total of the totals column. So, I want my output as such:
>
> > ----------------------------------
> > date total
> > ----------------------------------
> > 2007-10-12 20.00
> > 2007-10-13 0.00
> > 2007-10-14 3.62
> > 2007-10-15 6.48
> > ------------------------------------
> > Grand total 30.10
> > ------------------------------------
>
> > I've been able to get the sum for each day but I don't know how to get
> > the grand total. Here's what I've got so far:
>
> > select date, sum(amount) as TOTAL from my_expenses group by date;
>
> > That gives me something like this:
>
> > ----------------------------------
> > date total
> > ----------------------------------
> > 2007-10-12 20.00
> > 2007-10-13 0.00
> > 2007-10-14 3.62
> > 2007-10-15 6.48
>
> > Now I want the grand total as well. Please .
>
> This sort of thing is supplied by a query management facility, not by
> the query itself.
>
> You need to surround the query with some other application, or write
> it yourself.- Hide quoted text -
>
> - Show quoted text -



Thanks. So, that is not possible with an SQL query?

Water Cooler v2 wrote:
> On Oct 15, 5:02 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>> On 15 Oct, 16:50, Water Cooler v2 <wtr_...@yahoo.com> wrote:
>>
>>
>>
>>
>>
>>> I have a table called my_expenses that has the following fields:
>>
>>> id int AUTO_INCREMENT PRIMARY KEY,
>>> date date not null,
>>> item varchar(255) not null,
>>> amount decimal (4, 2) not null
>>
>>> I need to know the total amount of daily expenses I incur each day
>>> with a grand total of the totals column. So, I want my output as
>>> such:
>>
>>> ----------------------------------
>>> date total
>>> ----------------------------------
>>> 2007-10-12 20.00
>>> 2007-10-13 0.00
>>> 2007-10-14 3.62
>>> 2007-10-15 6.48
>>> ------------------------------------
>>> Grand total 30.10
>>> ------------------------------------
>>
>>> I've been able to get the sum for each day but I don't know how to
>>> get the grand total. Here's what I've got so far:
>>
>>> select date, sum(amount) as TOTAL from my_expenses group by date;
>>
>>> That gives me something like this:
>>
>>> ----------------------------------
>>> date total
>>> ----------------------------------
>>> 2007-10-12 20.00
>>> 2007-10-13 0.00
>>> 2007-10-14 3.62
>>> 2007-10-15 6.48
>>
>>> Now I want the grand total as well. Please .
>>
>> This sort of thing is supplied by a query management facility, not by
>> the query itself.
>>
>> You need to surround the query with some other application, or write
>> it yourself.- Hide quoted text -
>>
>> - Show quoted text -
>
>
>
> Thanks. So, that is not possible with an SQL query?

It would be possible to craft one using a UNION, but it really wouldn't be
worth it!