List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:September 17 1999 4:49pm
Subject:Re: acumulated amount
View as plain text  
"Manuel Coral A." wrote:
> 
> Hello,
> can someone tell me if is posible one query to  get this.?
> 
> my actual table:
> ===============
> 
> mysql> select code,country,total from transactions;
> +--------+---------+---------------+
> | code   | country | total         |
> +--------+---------+---------------+
> | 000007 | BO      |    1.0000     |
> | 000001 | CL      |    2.0000     |
> | 000008 | BO      |    3.0000     |
> | 000006 | BS      |    4.0000     |
> | 000005 | BS      |    5.0000     |
> +--------+---------+---------------+
> 5 rows in set (0.00 sec)
> 
> desired report:
> ==============
> 
> mysql> select <some special options> from transactions;
> +--------+---------+---------------+
> | code   | country | total         |
> +--------+---------+---------------+
> | 000007 | BO      |    1.0000     |
> | 000001 | CL      |    2.0000     |
> | 000008 | BO      |    3.0000     |
> | 000006 | BS      |    4.0000     |
> | 000005 | BS      |    5.0000     |
> | Total  |         |   15.0000     |
> +--------+---------+---------------+
> 6 rows in set (0.00 sec)
> 
> That's, the last row contain the sum of the above row
> 1.0000 + 2.0000 + 3.0000 + 4.0000 + 5.0000 = 15.0000 and with Total
> in the code field.
> 
> I am looking at the sum() function, and I can get the  mentioned sum,
> with one select  like:
> 
> mysql> select sum(total) from transactions where order_date=current_date;
> 
> +------------+
> | sum(total) |
> +------------+
> |     5.0000 |
> +------------+
> 1 row in set (0.00 sec)
> 
> taking my order_date field  as reference field, but, as you see, I don't
> get this result  in the desired form..
>  Is it, posible.?
> 
> Thanks in advance.
> 
> Manuel Coral.

Hi Manuel

You can't get the list and the total line in one query.
But you can get the "Total" row with:
SELCT 'Total' AS code, '' AS country, SUM( total ) AS total FROM transactions;

Tschau
Christian

Thread
acumulated amountManuel Coral A.16 Sep
  • Re: acumulated amountChristian Mack17 Sep