List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:April 30 2003 2:29am
Subject:Re: Select as sum() all records of today
View as plain text  
At 18:58 -0700 4/29/03, Scott Haneda wrote:
>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()));

It's easier to use this:

WHERE FROM_DAYS(TO_DAYS(added)) = CURDATE()

TO_DAYS() converts added to days, chopping off the time part in the
process, and FROM_DAYS() converts the results back to a date.  Then
you can simply compare it to CURDATE(), which is today's date.


>
>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: 415.898.2602
>http://www.newgeo.com                       Fax: 313.557.5052
>scott@stripped                            Novato, CA U.S.A.


-- 
Paul DuBois
http://www.kitebird.com/
sql, query
Thread
Installation QuestionSpenser E30 Apr
  • Select as sum() all records of todayScott Haneda30 Apr
    • Re: Select as sum() all records of todayScott Haneda30 Apr
      • Re: Select as sum() all records of todayPaul DuBois30 Apr
  • Re: Installation QuestionDan Crites30 Apr
RE: Select as sum() all records of todayLeong Jern-Kuan30 Apr
Re: Select as sum() all records of todayScott Haneda30 Apr
RE: Select as sum() all records of todayLeong Jern-Kuan30 Apr