#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    103

    Trying to get a count between a range of dates


    mysql> desc ClosedPeriodActivitySummary;
    +-------------+--------------------------------------+------+-----+-------------------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +-------------+--------------------------------------+------+-----+-------------------+-------+
    | FundId | int(11) | NO | MUL | NULL | |
    | BegDate | date | NO | MUL | NULL | |
    | EndDate | date | NO | MUL | NULL | |
    | Category | enum('CASH','MARKET') | YES | MUL | NULL | |
    | ItemName | varchar(32) | YES | MUL | NULL | |
    | Amount | decimal(16,2) | YES | | NULL | |
    | Direction | enum('INFLOW','OUTFLOW','NET-VALUE') | YES | MUL | NULL | |
    | LastUpdated | timestamp | NO | | CURRENT_TIMESTAMP | |
    +-------------+--------------------------------------+------+-----+-------------------+-------+


    Then I do the following select of:

    select count(*) from ClosedPeriodActivitySummary where BegDate < now() and BegDate > '2013-01-11';

    *** Count(*) 1313

    mysql> show table status like 'ClosedPeriodActivitySummary';
    +-----------------------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+-----------------------+
    | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
    +-----------------------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+-----------------------+
    | ClosedPeriodActivitySummary | InnoDB | 10 | Compact | 84921 | 105 | 8929280 | 0 | 21069824 | 0 | NULL | 2012-05-18 15:20:24 | NULL | NULL | latin1_swedish_ci | NULL | | InnoDB free: 15360 kB |
    +-----------------------------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+-----------------------+
    1 row in set (0.42 sec)


    *** Avg_row_length 89

    NOW that is in BYTES...so to get the size of a month's of data 1313 x 89 = 116857 bytes which is 0.11144 MB

    ISSUE: I wanted to test for a different time period, so I wrote a different select query and am getting a error:

    mysql> select count(*) from ClosedPeriodActivitySummary where BegDate between '2013-01-01' and BegDate '2013-01-31';
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2013-01-31'' at line 1


    So what am I doing wrong here...

    I am using Community version 5.0.51a MySQL

    I am just trying to obtain the count for the number of rows added in this table ClosedPeriodActivitySummary for the period of 2013-01-01 to 2013-01-31 how do I do that in syntax for my version of MySQL?
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    103
    I figured it out...

    mysql> select count(*) from ClosedPeriodActivitySummary where BegDate between '2013-01-01' and '2013-01-31';
    +----------+
    | count(*) |
    +----------+
    | 3120 |
    +----------+
    1 row in set (0.03 sec)

IMN logo majestic logo threadwatch logo seochat tools logo