I have a table storing payment transactions. They are stored with datetime in this way:
day-month-year hour:min:sec (Example: 18-11-2019 11:54:41)
I want to count how many transactions per day.
If I use this query:
SELECT COUNT(id), paid FROM `payments` WHERE paid IS NOT NULL GROUP BY paid
it returns me every single row, but I want to group by date, ignoring time.
If your column in an actual DATETIME, the DATE function should do:
SELECT COUNT(id), paid FROM `payments` WHERE paid IS NOT NULL GROUP BY DATE(paid)
If it is a VARCHAR or TEXT with a custom format, like the example you specified (18-11-2019 11:54:41), you'd need to parse it first using STR_TO_DATE:
SELECT COUNT(id), paid FROM `payments` WHERE paid IS NOT NULL GROUP BY DATE(STR_TO_DATE(paid, '%d-%m-%Y %H:%i:%s'))
You should always use the DATETIME column format to store dates, not VARCHAR. You can always deal with presentation issues (e.g. converting to European format) in your application layer. If that was the case you could simply GROUP BY DATE(paid) i.e.
SELECT COUNT(id), paid
FROM `payments`
WHERE paid IS NOT NULL
GROUP BY DATE(paid)
If your column is VARCHAR or TEXT you need to convert it to a value you can group by. If the format is DD-MM-YYYY, you can simply use LEFT(paid, 10) to extract the date portion and group by that i.e.
SELECT COUNT(id), paid
FROM `payments`
WHERE paid IS NOT NULL
GROUP BY LEFT(paid, 10)
If you're dates are stored as D-M-YYYY you will need to convert them using STR_TO_DATE e.g.
SELECT COUNT(id), paid
FROM `payments`
WHERE paid IS NOT NULL
GROUP BY STR_TO_DATE(paid, '%e-%c-%Y')
Note you can ignore the time portion of the value when converting and so avoid the need for the DATE function.
DATETIMEcolumn, then you'd just useGROUP BY DATE(paid)