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?
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;