Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Jul 2007
    Posts
    12

    Unanswered: easy for an expert ? missing rows in group by date query

    I have a this table:

    mysql> describe transaxtions;
    +---------------+--------------+------+-----+---------+----------------+
    | Field | Type | Null | Key | Default | Extra |
    +---------------+--------------+------+-----+---------+----------------+
    | id | int(11) | NO | PRI | NULL | auto_increment |
    | account_id | int(11) | NO | MUL | | |
    | category_id | int(11) | NO | MUL | | |
    | created_at | datetime | YES | | NULL | |
    | updated_at | datetime | YES | | NULL | |
    | note | varchar(255) | YES | MUL | | |
    | description | varchar(255) | YES | MUL | | |
    | memo | varchar(255) | YES | MUL | | |
    | amount | float | YES | MUL | 0 | |
    | merchant_id | int(11) | NO | | | |
    +---------------+--------------+------+-----+---------+----------------+


    And I need summary info count,total,average for an account over the last 12 months...

    I currently have this:

    SELECT
    MONTH(transaxtions.date) as month,
    DATE_FORMAT(transaxtions.date,'%Y-%m-01') as start_date,
    LAST_DAY(transaxtions.date) as end_date,
    sum(transaxtions.amount) as total_amount,
    avg(transaxtions.amount) as average_amount,
    count(transaxtions.id) as total_count
    FROM
    transaxtions
    WHERE (transaxtions.date >= '2006-07-01 00:00:00')
    GROUP BY MONTH(transaxtions.date)
    ORDER BY transaxtions.date


    This is returning good data for the months that have transactions. But some months have no transactions and hence get no result row returned.

    This has to be a common problem, but searching around has not helped much.

    As an added bonus, I'd like a column in the result set for how many months ago this data is for. Right now I have the month column, but that is for the month of the year.

    Thanks in advance experts @!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT m as month
         , DATE_FORMAT(transaxtions.date,'%Y-%m-01') as start_date
         , LAST_DAY(transaxtions.date) as end_date
         , sum(transaxtions.amount) as total_amount
         , avg(transaxtions.amount) as average_amount
         , count(transaxtions.id) as total_count 
      FROM ( select 1 as m union all 
             select 2 union all select 3 union all select 4
             select 5 union all select 6 union all select 7
             select 8 union all select 9 union all select 10
             select 11 union all select 12 ) as months
    LEFT OUTER
      JOIN transaxtions
        ON MONTH(transaxtions.date) = m  
     WHERE transaxtions.date >= '2006-07-01'
    GROUP 
        BY m
    ORDER 
        BY start_date
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2007
    Posts
    12

    thanks, but get a sql error

    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
    'select 5 union all select 6 union all select 7
    select 8 union all selec' at line 9

    I am not familiar with the union all syntax, will look it up.

  4. #4
    Join Date
    Jul 2007
    Posts
    12

    more info

    ok, found the missing "union all" statements, but this still does not return data for empty rows...

    SELECT m as month
    , DATE_FORMAT(transaxtions.date,'%Y-%m-01') as start_date
    , LAST_DAY(transaxtions.date) as end_date
    , sum(transaxtions.amount) as total_amount
    , avg(transaxtions.amount) as average_amount
    , count(transaxtions.id) as total_count
    FROM ( select 1 as m union all
    select 2 union all select 3 union all select 4 union all
    select 5 union all select 6 union all select 7 union all
    select 8 union all select 9 union all select 10 union all
    select 11 union all select 12 ) as months
    LEFT OUTER
    JOIN transaxtions
    ON MONTH(transaxtions.date) = m
    WHERE transaxtions.date >= '2006-07-01'
    GROUP
    BY m
    ORDER
    BY start_date


    here is what I get now...


    mysql> SELECT m as month
    -> , DATE_FORMAT(transaxtions.date,'%Y-%m-01') as start_date
    -> , LAST_DAY(transaxtions.date) as end_date
    -> , sum(transaxtions.amount) as total_amount
    -> , avg(transaxtions.amount) as average_amount
    -> , count(transaxtions.id) as total_count
    -> FROM ( select 1 as m union all
    -> select 2 union all select 3 union all select 4 union all
    -> select 5 union all select 6 union all select 7 union all
    -> select 8 union all select 9 union all select 10 union all
    -> select 11 union all select 12 ) as months
    -> LEFT OUTER
    -> JOIN transaxtions
    -> ON MONTH(transaxtions.date) = m
    -> WHERE transaxtions.date >= '2006-07-01'
    -> GROUP
    -> BY m
    -> ORDER
    -> BY start_date;
    +-------+------------+------------+-------------------+------------------+-------------+
    | month | start_date | end_date | total_amount | average_amount | total_count |
    +-------+------------+------------+-------------------+------------------+-------------+
    | 3 | 2007-03-01 | 2007-03-31 | 3700.30054092407 | 11.420680681864 | 324 |
    | 4 | 2007-04-01 | 2007-04-30 | 4901.86039757729 | 13.071627726873 | 375 |
    | 5 | 2007-05-01 | 2007-05-31 | 4289.21094655991 | 10.512771927843 | 408 |
    | 6 | 2007-06-01 | 2007-06-30 | 5173.60045814514 | 12.742858271293 | 406 |
    | 7 | 2007-07-01 | 2007-07-31 | -2104.87997531891 | -29.646196835478 | 71 |
    +-------+------------+------------+-------------------+------------------+-------------+

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, don't bother looking up UNION ALL, that's not your problem

    your problem is you're on a really ancient version of mysql

    (you're not still running windows 3.1, are you?

    run the following query:
    Code:
    create table months
    ( m integer not null primary key );
    insert into months values
    (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12);
    and then change your main query to:
    Code:
    SELECT m as month
         , DATE_FORMAT(transaxtions.date,'%Y-%m-01') as start_date
         , LAST_DAY(transaxtions.date) as end_date
         , sum(transaxtions.amount) as total_amount
         , avg(transaxtions.amount) as average_amount
         , count(transaxtions.id) as total_count 
      FROM months
    LEFT OUTER
      JOIN transaxtions
        ON MONTH(transaxtions.date) = m  
     WHERE transaxtions.date >= '2006-07-01'
    GROUP 
        BY m
    ORDER 
        BY start_date
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jul 2007
    Posts
    12

    mysql 5.0.38

    I am no version 5.0.38 (latest is 5.0.41), not too ancient.

    And no, I am not on 3.1 !!!

    me@feisty:~/workspace/rails_toucan_200$ mysql --version
    mysql Ver 14.12 Distrib 5.0.38, for pc-linux-gnu (i486) using readline 5.2

  7. #7
    Join Date
    Jul 2007
    Posts
    12
    new results below...still no data for "empty" rows...and this is a nicer way to get the month integers !!!

    mysql> insert into months values
    -> (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12) ;
    Query OK, 12 rows affected (0.00 sec)
    Records: 12 Duplicates: 0 Warnings: 0

    mysql> SELECT m as month
    -> , DATE_FORMAT(transaxtions.date,'%Y-%m-01') as start_date
    -> , LAST_DAY(transaxtions.date) as end_date
    -> , sum(transaxtions.amount) as total_amount
    -> , avg(transaxtions.amount) as average_amount
    -> , count(transaxtions.id) as total_count
    -> FROM months
    -> LEFT OUTER
    -> JOIN transaxtions
    -> ON MONTH(transaxtions.date) = m
    -> WHERE transaxtions.date >= '2006-07-01'
    -> GROUP
    -> BY m
    -> ORDER
    -> BY start_date;
    +-------+------------+------------+-------------------+------------------+-------------+
    | month | start_date | end_date | total_amount | average_amount | total_count |
    +-------+------------+------------+-------------------+------------------+-------------+
    | 3 | 2007-03-01 | 2007-03-31 | 3700.30054092407 | 11.420680681864 | 324 |
    | 4 | 2007-04-01 | 2007-04-30 | 4901.86039757729 | 13.071627726873 | 375 |
    | 5 | 2007-05-01 | 2007-05-31 | 4289.21094655991 | 10.512771927843 | 408 |
    | 6 | 2007-06-01 | 2007-06-30 | 5173.60045814514 | 12.742858271293 | 406 |
    | 7 | 2007-07-01 | 2007-07-31 | -2104.87997531891 | -29.646196835478 | 71 |
    +-------+------------+------------+-------------------+------------------+-------------+
    5 rows in set (0.01 sec)

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yeah, i figured out it wasn't an older version of mysql after the query ran, however, your error message in post #3 is exactly what you get if your version of mysql doesn't support subqueries

    anyhow, the reason you 're not getting all the months is because of the WHERE clause, which i should've spotted sooner

    change the word WHERE to AND
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jul 2007
    Posts
    12
    woohooo, data

    Last thing, I really want these in oldest to newest order, sorting on the date column does not work as that is null for the empty rows. Sorting on month only works at the end of the year !

    I'd really like a months ago column to sort on, and to not have to hardcode the start date...



    mysql> SELECT m as month
    -> , DATE_FORMAT(transaxtions.date,'%Y-%m-01') as start_date
    -> , LAST_DAY(transaxtions.date) as end_date
    -> , sum(transaxtions.amount) as total_amount
    -> , avg(transaxtions.amount) as average_amount
    -> , count(transaxtions.id) as total_count
    -> FROM ( select 1 as m union all
    -> select 2 union all select 3 union all select 4 union all
    -> select 5 union all select 6 union all select 7 union all
    -> select 8 union all select 9 union all select 10 union all
    -> select 11 union all select 12 ) as months
    -> LEFT OUTER
    -> JOIN transaxtions
    -> ON MONTH(transaxtions.date) = m
    -> AND transaxtions.date >= '2006-07-01'
    -> GROUP
    -> BY m
    -> ORDER
    -> BY start_date;
    +-------+------------+------------+-------------------+------------------+-------------+
    | month | start_date | end_date | total_amount | average_amount | total_count |
    +-------+------------+------------+-------------------+------------------+-------------+
    | 12 | NULL | NULL | NULL | NULL | 0 |
    | 8 | NULL | NULL | NULL | NULL | 0 |
    | 9 | NULL | NULL | NULL | NULL | 0 |
    | 1 | NULL | NULL | NULL | NULL | 0 |
    | 10 | NULL | NULL | NULL | NULL | 0 |
    | 2 | NULL | NULL | NULL | NULL | 0 |
    | 11 | NULL | NULL | NULL | NULL | 0 |
    | 3 | 2007-03-01 | 2007-03-31 | 3700.30054092407 | 11.420680681864 | 324 |
    | 4 | 2007-04-01 | 2007-04-30 | 4901.86039757729 | 13.071627726873 | 375 |
    | 5 | 2007-05-01 | 2007-05-31 | 4289.21094655991 | 10.512771927843 | 408 |
    | 6 | 2007-06-01 | 2007-06-30 | 5173.60045814514 | 12.742858271293 | 406 |
    | 7 | 2007-07-01 | 2007-07-31 | -2104.87997531891 | -29.646196835478 | 71 |
    +-------+------------+------------+-------------------+------------------+-------------+
    12 rows in set (0.11 sec)

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    are you sure you even want the month number?

    try this --
    Code:
    SELECT start_date
         , LAST_DAY(start_date) as end_date
         , sum(transaxtions.amount) as total_amount
         , avg(transaxtions.amount) as average_amount
         , count(transaxtions.id) as total_count
      FROM (
           SELECT DATE_SUB(
               DATE_ADD(LAST_DAY(current_date), INTERVAL 1 DAY)
                  INTERVAL m MONTH) as start_date
             FROM ( select 1 as m union all
             select 2 union all select 3 union all select 4 union all
             select 5 union all select 6 union all select 7 union all
             select 8 union all select 9 union all select 10 union all
             select 11 union all select 12 ) as months
           ) as start_dates 
    LEFT OUTER
      JOIN transaxtions
        ON transaxtions.date BETWEEN start_date
                        AND LAST_DAY(start_date)
    GROUP
        BY start_date
    ORDER
        BY start_date;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jul 2007
    Posts
    12
    Your right, I don't really need the month...

    I get this sql error now...

    Boy, I really need to brush up on my sql here, thanks alot for helping me out here so patiently.



    mysql> SELECT start_date
    -> , LAST_DAY(start_date) as end_date
    -> , sum(transaxtions.amount) as total_amount
    -> , avg(transaxtions.amount) as average_amount
    -> , count(transaxtions.id) as total_count
    -> FROM (
    -> SELECT DATE_SUB(
    -> DATE_ADD(LAST_DAY(current_date), INTERVAL 1 DAY)
    -> INTERVAL 1 MONTH) as start_date
    -> FROM ( select 1 as m union all
    -> select 2 union all select 3 union all select 4 union all
    -> select 5 union all select 6 union all select 7 union all
    -> select 8 union all select 9 union all select 10 union all
    -> select 11 union all select 12 ) as months
    -> ) as start_dates
    -> LEFT OUTER
    -> JOIN transaxtions
    -> ON transaxtions.date BETWEEN start_date
    -> AND LAST_DAY(start_date)
    -> GROUP
    -> BY start_date
    -> ORDER
    -> BY start_date;
    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
    'INTERVAL 1 MONTH) as start_date
    FROM ( select 1 as m union all
    ' at line 9

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oops, my bad, please insert a comma right in front of INTERVAL 1 MONTH
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jul 2007
    Posts
    12
    Ok, you have really made my day here. I never realized the magic you could do with datetime functions.

    I am sure this post will help others in the future.

    Is there anyway to reward people on this forum ???

  14. #14
    Join Date
    Jul 2007
    Posts
    12
    Ok, one more...I added the missing comma...now I am trying to limit the transaxtions to those for a particular account via the WHERE clause I added below...of course it does not like my syntax, and it did not seem write when I typed it in...




    SELECT start_date
    , LAST_DAY(start_date) as end_date
    , sum(transaxtions.amount) as total_amount
    , avg(transaxtions.amount) as average_amount
    , count(transaxtions.id) as total_count
    FROM (
    SELECT DATE_SUB(
    DATE_ADD(LAST_DAY(current_date), INTERVAL 1 DAY),
    INTERVAL m MONTH) as start_date
    FROM ( select 1 as m union all
    select 2 union all select 3 union all select 4 union all
    select 5 union all select 6 union all select 7 union all
    select 8 union all select 9 union all select 10 union all
    select 11 union all select 12 ) as months
    ) as start_dates
    LEFT OUTER
    JOIN transaxtions
    ON transaxtions.date BETWEEN start_date
    AND LAST_DAY(start_date)
    WHERE transaxtions.account_id = 21
    GROUP
    BY start_date
    ORDER
    BY start_date;

  15. #15
    Join Date
    Jul 2007
    Posts
    12
    To be more clear, the syntax appears to be fine, but I no longer get my much wanted empty rows !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •