CODEWITHSUNDEEP
phpmysql
Max90
Max90

Reputation: 193

MySql COUNT() as field returns only one entry

I have been trying to figure figure this one out for hours now, but I cannot wrap my head around why it won't work.

I have a MySQL query which looks like this:

SELECT
    *,
    COUNT(Schedule.id) AS schedule_count,
    GetDistance(49.0134297, 12.1016236, Provider.latitude, Provider.longitude) AS distance,
    Provider.gps_modified >= DATE_SUB(NOW(), INTERVAL 180 MINUTE) AS valid_gps,
    `Provider`.`id`
FROM `db`.`providers` AS `Provider` 
LEFT JOIN `availapple`.`employments` AS `Employment` ON (`Provider`.`employment_id` = `Employment`.`id`)
INNER JOIN `db`.`certificates_providers` AS `CertProv` ON (`CertProv`.`provider_id` = `Provider`.`id`)
INNER JOIN `db`.`schedules` AS `Schedule` ON (`Schedule`.`provider_id` = 
`Provider`.`id`)
WHERE `Provider`.`id` IN (1, 19, 23, 28, 48, 63, 96, 97, 126, 128, 150, 157, 172)
AND `Provider`.`last_login` >= DATE_SUB(NOW(), INTERVAL 180000 MINUTE)
ORDER BY `Provider`.`activity_points` DESC, `schedule_count` DESC
LIMIT 10

There are 10 providers which match the conditions specified in the WHERE clause, however I only get one result. If i get rid of the COUNT(Schedule.id) AS schedule_count however it returns all 10 results.

Upvotes: 1

Views: 44

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

You should add the group by Provider.id

this way 

SELECT
    *,
    COUNT(Schedule.id) AS schedule_count,
    GetDistance(49.0134297, 12.1016236, Provider.latitude, Provider.longitude) AS distance,
    Provider.gps_modified >= DATE_SUB(NOW(), INTERVAL 180 MINUTE) AS valid_gps,
    `Provider`.`id`
FROM `db`.`providers` AS `Provider` 
LEFT JOIN `availapple`.`employments` AS `Employment` ON (`Provider`.`employment_id` = `Employment`.`id`)
INNER JOIN `db`.`certificates_providers` AS `CertProv` ON (`CertProv`.`provider_id` = `Provider`.`id`)
INNER JOIN `db`.`schedules` AS `Schedule` ON (`Schedule`.`provider_id` = 
`Provider`.`id`)
WHERE `Provider`.`id` IN (1, 19, 23, 28, 48, 63, 96, 97, 126, 128, 150, 157, 172)
AND `Provider`.`last_login` >= DATE_SUB(NOW(), INTERVAL 180000 MINUTE)
group by  `Provider`.`id`
ORDER BY `Provider`.`activity_points` DESC, `schedule_count` DESC
LIMIT 10

Upvotes: 2

Related Questions