Re: Select as sum() all records of today

[Scott Haneda]

on 04/29/2003 6:28 PM, Scott Haneda at lists@stripped wrote:

> I have the following table transaction
> +------------+---------------+------+-----+---------+----------------+
> | Field      | Type          | Null | Key | Default | Extra          |
> +------------+---------------+------+-----+---------+----------------+
> | id         | int(11)       |      | PRI | NULL    | auto_increment |
> | user_id    | int(11)       |      |     | 0       |                |
> | amount     | decimal(6,2)  |      |     | 0.00    |                |
> | trans_type | varchar(12)   |      |     |         |                |
> | error      | varchar(80)   |      |     |         |                |
> | added      | timestamp(14) | YES  |     | NULL    |                |
> +------------+---------------+------+-----+---------+----------------+
> 
> I need to do a
> Select SUM(amount) from transaction WHERE added ..... Is in todays data
> range.  Can someone help me with this one, thanks.

I think I got it, I am not sure this is the best way...
select SUM(amount) from transaction where added >= CONCAT(YEAR(NOW()), '-',
MONTH(NOW()),'-', DAYOFMONTH(NOW()));

I had to CONCAT a '-' in there because the month and day are returned as
single digits when they are less then 10, is there a way to do it perhaps
more like...

select SUM(amount) from transaction where added >= CONCAT(YEAR(NOW()) as
INT(2), '-', MONTH(NOW()),'-', DAYOFMONTH(NOW()) as INT(2));

Or something like that, excuse my guesswork syntax, I can not locate this in
the manual, so I have been CONCAT'ing the '-' for the time being.

-------------------------------------------------------------
Scott Haneda                                Tel: [phone]
http://www.newgeo.com                       Fax: [phone]
scott@stripped                            Novato, CA U.S.A.