Adding count(*) to SQL Server query

Question

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?

Answer 1

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;

Answer 2

Count(*) is going to make your query an aggregate and so, unless you use a group by, the result will be a single row. The error message is saying that you cant't use the other fields and still generate a single row

Answer 3

You can not use COUNT(*) without GROUP BY or OVER (PARTITION BY ...)

Try to alter your query to

COUNT(*) OVER () AS rCount,

OR

COUNT(*) OVER (PARTITION BY ColumnName) AS rCount,

Answer 4

If you need to count the number of times the same row occours, just add a group by clause with all the fields you need to select, so:

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
group by id, 'Event Location', venueName, venueLatLng
) TMP 
WHERE distance < 30 
ORDER BY distance;

Otherwise, if you need the total number of rows, the best thing to do is to count them with the script language (PHP for instance); or if you strictly need to do it with SQL you need to add a window on which calculate the count, so add

 count(*) over ()

but in this case you have the count value repeated for each row returned.

Answer 5

You have to add a GROUP BY to the elements that are not in the COUNT(1). For your query, you need to decide what you are counting. Adding a count, but grouping by the primary key, will guarantee each record grouping is queue, so the COUNT will always be 1. If you just need a COUNT by for instance venueName, then your query could use the query below and adjust it to the elements that need to be counted within the grouping (such as add venue lat/long, etc).

SELECT venueName, COUNT(1) FROM marker GROUP BY venueName

Answer 6

Why do you not use @@ROWCOUNT to get the resulting row count from the query.

http://msdn.microsoft.com/en-us/library/ms187316.aspx