CODEWITHSUNDEEP
sql
RGriffiths
RGriffiths

Reputation: 5970

Counting records SQL

I am trying to count the number of records for each user:

SELECT count(userID) as countOfRecords FROM (select distinct userID from `table`)

I get the error: Every derived table must have its own alias What am I doing wrong?

Upvotes: 1

Views: 64

Answers (3)

Tim Schmelter
Tim Schmelter

Reputation: 460288

Just for the sake of completeness and because MySql was not tagged explicitly. If you use SQL-Server you could also use COUNT(*) OVER. Then you get the number of records for each UserID in every row:

SELECT userID,
       COUNT(*) OVER (PARTITON BY userID) AS countOfRecords, 
       other columns ...
FROM dbo.TableName

Upvotes: 0

Philip Fourie
Philip Fourie

Reputation: 117037

You can use GROUP BY

select userID, count(*) as countOfRecords
from table
group by userID

Upvotes: 2

jarlh
jarlh

Reputation: 44795

You don't need a derived table, you can just do count(distinct):

SELECT count(distinct userID) as countOfRecords
FROM `table`

Edit: When I see Philip Fourie's answer I realize I perhaps didn't give the complete answer... See his GROUP BY solution as well.

Upvotes: 2

Related Questions