Time syntax question for calculating a set of values

[LearnFromGuru]

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

 

[r937]

let's first of all clear up the issue of why nothing was returned

you had --

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

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' 
    [Blue][B]OR[/B][/blue] PaymentDate BETWEEN '2012-07-01' AND '2012-07-31' 
GROUP
    BY DATE_FORMAT(PaymentDate,'2012-%m')

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon