SQL SELECT query return total COUNT when filtering with WHERE clause

Question

I need a select query from two tables, Car and CarImage. There will be multiple images for each car. I want to return the total image count and the image name for just the first image (DisplayOrder = 1) for each car, but adding this where clause results in the image count always equal to one. How can I return the total image count and image name of just the first image for each car?

My simplified query looks like this:

SELECT c.Id,
        c.Make,
        c.Model,
        ci.ImageName,
        COUNT(ci.ImageName) AS ImageCount
FROM Car c
JOIN CarImage ci
ON c.Id = ci.CarId
WHERE ci.DisplayOrder = 1
GROUP BY (ci.ImageName) <-- usually need some kind of `GROUP BY` to get count

Answer 1

It's a bit clunky, but you can get there with conditional aggregation. Because null will always be less than non-null.

 SELECT c.Id,
        c.Make,
        c.Model,
        max(case when ci.displayOrder = 1 then ci.ImageName end) as first_image_name,
        COUNT(ci.ImageName) AS ImageCount
   FROM Car c
   JOIN CarImage ci
     ON c.Id = ci.CarId
  GROUP
     BY c.Id,
        c.Make,
        c.Model