Reputation: 1
I have a MySQL table which has a payment_date row. I'm getting the rows from date X and date Y. I would like to print the number of occurrences per date.
For example:
payment_date
2011-07-25
2011-07-24
2011-07-25
2011-06-13
I would need to return that in:
2011-07-25 got 2 payments
2011-07-24 got 1 payments
2011-06-13 got 1 payment
With this info I'm doing a plot, with X axis: date, and Y axis: number of payments.
My query by now is:
SELECT *
FROM table_pay
WHERE DATE_FORMAT(payment_date, '%m/%d/%Y')
BETWEEN '$payment_date_from'
AND
DATE '$payment_date_to'
GROUP BY DATE(payment_date)
[don't know!!!!]
Any help, please?
Upvotes: 0
Views: 60
Reputation: 377
I would use a query like the following (assuming payment_date is a date) :
Select payment_date, count(*) as paymentcount from table_pay where payment_date between 'startdate' and 'enddate' group by payment_date
Upvotes: 0
Reputation: 62484
Try out this, can't test it right now so please respond whether it works as you are expect
SELECT DATE_FORMAT(payment_date, '%Y-%m-%d'), COUNT(*)
FROM table_pay
WHERE payment_date BETWEEN '$payment_date_from' AND '$payment_date_to'
GROUP BY payment_date
Upvotes: 0
Reputation: 56357
SELECT
payment_date,count(*) as tot
FROM table_pay
WHERE payment_date
BETWEEN '$payment_date_from'
AND
'$payment_date_to'
GROUP BY payment_date
order by tot desc
Upvotes: 1