sqlsql-serverdatabaserecord-count

Adding count(*) to SQL Server query


I am trying to find a way to add a count to the output of my query:

SELECT *
FROM 
    (SELECT 
        id, 
        'Event Location' AS name, 
        venueName AS snippet, 
        venueLatLng AS coordinates, 
        (3959 
        * acos(cos(radians('xx.xxxxxx')) 
        * cos(radians(SUBSTRING(venueLatLng, 1, CHARINDEX(',', venueLatLng)-1))) 
        * cos(radians(SUBSTRING(venueLatLng, CHARINDEX(',', venueLatLng) + 1, 1000)) 
        - radians('-xx.xxxxxxx')) 
        + sin(radians('xx.xxxxxx')) 
        * sin(radians(SUBSTRING(venueLatLng, 1, CHARINDEX(',', venueLatLng)-1))))) AS distance 
     FROM marker) TMP 
WHERE 
    distance < 30 
ORDER BY 
    distance;

The way I tried doing it was:

SELECT *
FROM (
SELECT  id, 
        'Event Location' AS name, 
        venueName AS snippet, 
        venueLatLng AS coordinates, 
        COUNT(*) AS rCount, 
        (3959 
        * acos(cos(radians('xx.xxxxxx')) 
        * cos(radians(SUBSTRING(venueLatLng, 1, CHARINDEX(',', venueLatLng)-1))) 
        * cos(radians(SUBSTRING(venueLatLng, CHARINDEX(',', venueLatLng) + 1, 1000)) 
        - radians('-xx.xxxxxxx')) 
        + sin(radians('xx.xxxxxx')) 
        * sin(radians(SUBSTRING(venueLatLng, 1, CHARINDEX(',', venueLatLng)-1))))) AS distance 
FROM marker) TMP 
WHERE distance < 30 
ORDER BY distance;

The error is this:

Column 'marker.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

How can I go about doing this correctly?


Solution

  • Use the analytic version of COUNT, and make sure to put it in your outer query so it will respect the WHERE distance < 30.

    SELECT
      TMP.*,
      COUNT(*) OVER () AS RCount
    FROM (
      SELECT  id, 
        'Event Location' AS name, 
        venueName AS snippet, 
        venueLatLng AS coordinates, 
        (3959 
        * acos(cos(radians('xx.xxxxxx')) 
        * cos(radians(SUBSTRING(venueLatLng, 1, CHARINDEX(',', venueLatLng)-1))) 
        * cos(radians(SUBSTRING(venueLatLng, CHARINDEX(',', venueLatLng) + 1, 1000)) 
        - radians('-xx.xxxxxxx')) 
        + sin(radians('xx.xxxxxx')) 
        * sin(radians(SUBSTRING(venueLatLng, 1, CHARINDEX(',', venueLatLng)-1))))) AS distance 
      FROM marker) TMP 
    WHERE distance < 30 
    ORDER BY distance;