Gregor
Gregor

Reputation: 1

MySQL date range: ocurrences among same dates

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

Answers (3)

Serkan Özkan
Serkan Özkan

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

sll
sll

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

Nicola Cossu
Nicola Cossu

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

Related Questions