How to get count of records using group by clause

4299 views mysql
3

I am trying to get the daily/date wise count of installed meters. The query is below.

SELECT COUNT(ins.`id`) AS Total, DATE(ins.`sync_date`) AS 'Date Time' FROM `installations` ins
GROUP BY ins.`sync_date`
ORDER BY ins.`sync_date` DESC

Output

enter image description here

It's giving me a single value against the date all I want is the total count per day.

Any help would be highly appreciated.

answered question

2 Answers

8

You need to use DATE(ins.sync_date) instead of ins.sync_date

SELECT COUNT(ins.`id`) AS Total, DATE(ins.`sync_date`) AS 'Date Time' 
FROM `installations` ins
GROUP BY DATE(ins.`sync_date`)
ORDER BY DATE(ins.`sync_date`) DESC

posted this
1

You need to group by DATE(ins.sync_date) because of you ins.sync_date is contained hour minute and second.

SELECT COUNT(ins.`id`) AS Total, DATE(ins.`sync_date`) AS 'Date Time' 
FROM `installations` ins
GROUP BY DATE(ins.`sync_date`)
ORDER BY ins.`sync_date` DESC

posted this

Have an answer?

JD

Please login first before posting an answer.