Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2012
    Posts
    3

    Unanswered: Time syntax question for calculating a set of values

    Hello All,
    I have a question about determining average data values for a specific time interval (a month in this case ) , for a given set of data, to create a table.
    I started with 1 months data (for September 2012), & it gave me the following result:

    mysql> SELECT DATE_FORMAT(PaymentDate, '2012-%-%') AS "Month", AVG(PaymentAmt)
    AS "Average Payment" FROM Payments WHERE PaymentDate BETWEEN '2012-09-01' AND
    '2012-09-30' ;
    +---------+-----------------+
    | Month | Average Payment |
    +---------+-----------------+
    | 2012--% | 100.000000 |
    +---------+-----------------+
    1 row in set (0.00 sec)

    I then proceeded to add an extra payment (BETWEEN) range, but instead of getting another row of values, I got the null set

    mysql> SELECT DATE_FORMAT(PaymentDate, '2012-%-%') AS "Month", AVG(PaymentAmt)
    AS "Average Payment" FROM Payments WHERE PaymentDate BETWEEN '2012-09-01' AND
    '2012-09-30' AND PaymentDate BETWEEN '2012-07-01' AND '2012-07-31';
    +-------+-----------------+
    | Month | Average Payment |
    +-------+-----------------+
    | NULL | NULL |
    +-------+-----------------+
    1 row in set (0.00 sec)

    I would greatly appreciate it if you could please tell me what I need to do to create a table of average values for each month.
    Thanks,
    John Smith

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    let's first of all clear up the issue of why nothing was returned

    you had --
    Code:
     WHERE PaymentDate BETWEEN '2012-09-01' AND '2012-09-30' 
       AND PaymentDate BETWEEN '2012-07-01' AND '2012-07-31'
    look at this carefully, and you will soon see (i trust) that if the PaymentDate falls within the first range, then it cannot possible ~also~ fall within the second range, and vice versa

    for more than one result row, you also need to use GROUP BY
    Code:
    SELECT DATE_FORMAT(PaymentDate,'2012-%m') AS "Month"
         , AVG(PaymentAmt) AS "Average Payment" 
      FROM Payments 
     WHERE PaymentDate BETWEEN '2012-09-01' AND '2012-09-30' 
        OR PaymentDate BETWEEN '2012-07-01' AND '2012-07-31' 
    GROUP
        BY DATE_FORMAT(PaymentDate,'2012-%m')
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2012
    Posts
    3

    It worked, Thanks Rudy

    Hi Rudy,
    Thank you for your help, it worked well, & I will definitely bookmark your site & check out your book. One mistake I made was that I forgot to mention that the data was from 2 different years, so I had to write 2 different select statements. They were as follows:

    mysql> SELECT DATE_FORMAT (PaymentDate, '2012-%m' ) AS "Month" ,
    AVG (PaymentAmt) AS "Average Payment"
    FROM Payments
    WHERE PaymentDate BETWEEN '2012-04-01' AND '2012-04-30'
    OR PaymentDate BETWEEN '2012-05-01' AND '2012-05-31'
    OR PaymentDate BETWEEN '2012-06-01' AND '2012-06-30'
    OR PaymentDate BETWEEN '2012-07-01' AND '2012-07-31'
    OR PaymentDate BETWEEN '2012-08-01' AND '2012-08-31'
    OR PaymentDate BETWEEN '2012-09-01' AND '2012-09-30'
    OR PaymentDate BETWEEN '2012-10-01' AND '2012-10-31'
    OR PaymentDate BETWEEN '2012-11-01' AND '2012-11-30'
    OR PaymentDate BETWEEN '2012-12-01' AND '2012-12-31'
    GROUP
    BY DATE_FORMAT (PaymentDate, '2012-%m') ;

    +---------+-----------------+
    | Month | Average Payment |
    +---------+-----------------+
    | 2012-04 | 560.000000 |
    | 2012-05 | 366.666667 |
    | 2012-06 | 512.500000 |
    | 2012-07 | 444.594595 |
    | 2012-08 | 126.470588 |
    | 2012-09 | 100.000000 |
    | 2012-10 | 100.000000 |
    | 2012-11 | 100.000000 |
    | 2012-12 | 200.000000 |
    +---------+-----------------+
    9 rows in set (0.00 sec)

    mysql> SELECT DATE_FORMAT (PaymentDate, '2013-%m' ) AS "Month" ,
    AVG (PaymentAmt) AS "Average Payment"
    FROM Payments
    WHERE PaymentDate BETWEEN '2013-01-01' AND '2013-01-31'
    OR PaymentDate BETWEEN '2013-07-01' AND '2013-07-31'
    GROUP
    BY DATE_FORMAT (PaymentDate, '2013-%m') ;
    +---------+-----------------+
    | Month | Average Payment |
    +---------+-----------------+
    | 2013-01 | 175.000000 |
    | 2013-07 | 83.333333 |
    +---------+-----------------+
    2 rows in set (0.03 sec)

    mysql>

    Regards,

    John

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why do you believe you need the complex where clause
    Code:
    WHERE PaymentDate BETWEEN '2012-04-01' AND '2012-04-30'
    OR PaymentDate BETWEEN '2012-05-01' AND '2012-05-31'
    OR PaymentDate BETWEEN '2012-06-01' AND '2012-06-30'
    OR PaymentDate BETWEEN '2012-07-01' AND '2012-07-31'
    OR PaymentDate BETWEEN '2012-08-01' AND '2012-08-31'
    OR PaymentDate BETWEEN '2012-09-01' AND '2012-09-30'
    OR PaymentDate BETWEEN '2012-10-01' AND '2012-10-31'
    OR PaymentDate BETWEEN '2012-11-01' AND '2012-11-30'
    OR PaymentDate BETWEEN '2012-12-01' AND '2012-12-31'
    when the above could be represented as
    Code:
    WHERE PaymentDate BETWEEN '2012-04-01' AND '2012-12-31'
    if you need to pull out specific months you could use the month and year functions

    Code:
    where month(paymentdate) = 4 and year(paymendate) = 2012
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by healdem View Post
    if you need to pull out specific months you could use the month and year functions

    Code:
    where month(paymentdate) = 4 and year(paymendate) = 2012
    true, you could... but using a function would then ignore the index on that column and likely be a lot slower

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    fair enough so
    so the previous suggested
    Code:
    WHERE PaymentDate BETWEEN '2012-04-01' AND '2012-12-31'
    should suffice
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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