how to calculate count in sql?

Question

I have the following table:

memberid  
2
2
3
4
3

...and I want the following result:

memberid    count
2           2
3           1    ---Edit by gbn: do you mean 2?
4           1

I was attempting to use:

  SELECT MemberID, 
         COUNT(MemberID) 
    FROM YourTable 
GROUP BY MemberID

...but now I want find which record which has maximum count. IE:

memberid   count
2          2

Answer 1

SELECT MemberID, COUNT(MemberID) FROM YourTable GROUP BY MemberID

Answer 2

SELECT memberid, COUNT(*) FROM TheTable GROUP BY memberid

Although, it won't work for your desired output because you have "memberid = 3" twice.

Edit: After late update to question...

SELECT TOP 1 WITH TIES    --WITH TIES will pick up "joint top". 
    memberid, COUNT(*)
FROM
    TheTable 
GROUP BY 
    memberid
ORDER BY
    COUNT(*) DESC

Answer 3

SELECT count(column_name) FROM your_table;

Answer 4

Do it like this:

SELECT memberid, COUNT(memberid) AS [count] FROM [Table] GROUP BY memberid

Answer 5

What if there is a tie (or more) for the max? Do you want to display one or all?

This is how I would do this

SELECT memberid, COUNT(1)
FROM members
GROUP BY memberid
HAVING COUNT(1) = (
      SELECT MAX(result.mem_count)
      FROM ( 
       SELECT memberid, COUNT(1) as mem_count
       FROM members
       GROUP BY memberid
      ) as result
       )

I would love to see a more efficient approach though.

Answer 6

You need to use a subselect:

SELECT MemberID, MAX(Count) FROM
    (SELECT MemberID, COUNT(MemberID) Count FROM YourTable GROUP BY MemberID)
GROUP BY MemberID

The second group by is needed to return both, the count and the MemberID.

Answer 7

This should do the trick with no subselects required:

select top 1 memberid, COUNT(*) as counted 
from members
group by memberid
order by counted desc

Answer 8

Can be done quite easy:

SELECT TOP 1 MemberId, COUNT(*) FROM YourTable GROUP BY MemberId ORDER By 2 DESC

Answer 9

I believe the original poster requested 2 result sets.

The only way I know of to get this (in SQL Server) is to dump the original records into a temp table and then do a SELECT and MAX on that. I do welcome an answer that requires less code!

-- Select records into a temp table
SELECT
    Table1.MemberId
    ,CNT = COUNT(*)
INTO #Temp
FROM YourTable AS Table1
GROUP BY Table1.MemberId
ORDER BY Table1.MemberId

-- Get original records
SELECT * FROM #Temp

-- Get max. count record(s) 
SELECT 
    Table1.MemberId
    ,Table1.CNT
FROM #Temp AS Table1
INNER JOIN (
    SELECT CNT = MAX(CNT)
    FROM #Temp
) AS Table2 ON Table2.CNT = Table1.CNT

-- Cleanup 
DROP TABLE #Temp

Answer 10

How about:

SELECT TOP 1 MemberID, COUNT(MemberID) FROM YourTable GROUP BY MemberID ORDER by count(MemberID) desc