CODEWITHSUNDEEP
sql-serversqlcount
David Folksman
David Folksman

Reputation: 235

Proper Use of COUNT to count number of rows returned in a SELECT statement

SELECT FirstName,
       MiddleName,
       LastName,
       COUNT(*) AS 'Count'
FROM Person.Person TABLESAMPLE(10 PERCENT)

Gives me the error

Msg 8120, Level 16, State 1, Line 1 Column 'Person.Person.FirstName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Could someone explain what I'm doing wrong?

Upvotes: 1

Views: 2129

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280625

SELECT FirstName, MiddleName, LastName, 
  [count] = COUNT(*) OVER()
 FROM Person.Person 
 TABLESAMPLE (10 PERCENT);

Though you may prefer the following, since TABLESAMPLE has some issues IIRC (for me, TABLESAMPLE returns an unpredictable number of rows every time, and it also prevents you from counting rows from the entire table):

SELECT TOP 10 PERCENT FirstName, MiddleName, LastName,
  [count] = COUNT(*) OVER()
 FROM Person.Person 
 ORDER BY [something];

If you really want a random set of rows, you can use ORDER BY NEWID();. And if you want the actual count returned by the query, not the count of rows in the actual table, just follow your query (without the COUNT aggregate) with:

SELECT @@ROWCOUNT;

Also please don't use 'single quotes' for column aliases - this syntax is deprecated in some cases and also makes aliases look like string literals. When you need to escape aliases because they are keywords or contain spaces or otherwise violate identifier rules, use [square brackets].

Upvotes: 4

Fábio Galera
Fábio Galera

Reputation: 125

Everytime you are going to use any aggregate function (sum, count, avg, etc), you must identify your GROUP clause;

In your query, you are identifying how many rows you have for each Firstname, Middlename, and lastname:

    SELECT FirstName,
    MiddleName,
    LastName,
    COUNT(*) AS 'Count'
    FROM Person.Person TABLESAMPLE(10 PERCENT)
    GROUP BY FirstName, MiddleName,LastName;

If you want to know how many person you have:

   SELECT COUNT(*) AS 'Count'
   FROM Person.Person TABLESAMPLE(10 PERCENT);

Upvotes: 2

Related Questions