CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sqlaggregate
Mansi
Mansi

Reputation: 151

how to calculate count in sql?

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

Upvotes: 0

Views: 2293

Answers (10)

user187783
user187783

Reputation:

How about this query:

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

Upvotes: 0

gbn
gbn

Reputation: 432311

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

Upvotes: 12

Yoav
Yoav

Reputation: 170

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

Upvotes: 0

erikkallen
erikkallen

Reputation: 34401

Can be done quite easy:

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

Upvotes: 0

Alex Bagnolini
Alex Bagnolini

Reputation: 22382

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

Upvotes: 0

Oliver Hanappi
Oliver Hanappi

Reputation: 12346

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.

Upvotes: -1

Hassan Voyeau
Hassan Voyeau

Reputation: 3624

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.

Upvotes: 1

J. Random Coder
J. Random Coder

Reputation: 1342

Do it like this:

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

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166406

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

Upvotes: 1

Davit Siradeghyan
Davit Siradeghyan

Reputation: 6323

SELECT count(column_name) FROM your_table;

Upvotes: -1

Related Questions