CODEWITHSUNDEEP
mysql
Jeff
Jeff

Reputation: 23

mysql count the number of matches based on a column

This is my example dataset I have groups with students assigned to them as shown below

uid | groupid         | studentid
49  | PZV7cUZCnLwNkSS | wTsBSkkg4Weo8R3
50  | PZV7cUZCnLwNkSS | aIuDhxfChg3enCf
97  | CwvkffFcBCRbzdw | hEwLxJmnJmZFAic
99  | CwvkffFcBCRbzdw | OKFfl58XVQMrAyC
126 | CwvkffFcBCRbzdw | dlH8udyTjNV3nXM
142 | 2vu1eqTCWVjgE58 | Q01Iz3lC2uUMBSB
143 | 2vu1eqTCWVjgE58 | vB5s8hfTaVtx3wO
144 | 2vu1eqTCWVjgE58 | 5O9HA5Z7wVhgi6l
145 | 2vu1eqTCWVjgE58 | OiEUOXNjK2D2s8F

I am trying to output with the following information. The problem I am having is the Group Size column getting it to output a count.

Studentid       | Groupid         | Group Size
wTsBSkkg4Weo8R3 | PZV7cUZCnLwNkSS | 2
aIuDhxfChg3enCf | PZV7cUZCnLwNkSS | 2
hEwLxJmnJmZFAic | CwvkffFcBCRbzdw | 3
OKFfl58XVQMrAyC | CwvkffFcBCRbzdw | 3
dlH8udyTjNV3nXM | CwvkffFcBCRbzdw | 3

I have researched if I can you can use a where clause in the count, and does not seem like it will let me do that. I thought about doing a sum but couldn't make that happen either. I feel like I am missing something simple.

Upvotes: 1

Views: 153

Answers (4)

asmgx
asmgx

Reputation: 7984

try this

SELECT T1.Studentid, T1.Groupid, T2.GroupCount
FROM Your_Table T1
INNER JOIN ( SELECT Groupid, count(*) AS GroupCount FROM Your_Table GROUP BY Groupid ) T2
ON T1.Groupid = T2.Groupid

Upvotes: 1

chickahoona
chickahoona

Reputation: 2034

Try this:

SELECT *
FROM pony a
LEFT JOIN (
    SELECT COUNT(*), groupid
    FROM pony
    GROUP BY groupid
) b ON a.groupid = b.groupid

Upvotes: 1

user8128312
user8128312

Reputation:

You should try:

SELECT COUNT(Groupid) AS Groupsize FROM table;

It seems that what you're trying to do is simple. If I understand correctly, a simple SELECT COUNT statement. To exclude multiple returns of the same value, use SELECT DISTINCT COUNT()

Upvotes: 0

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476547

An easy way to solve this, is using a JOIN statement:

SELECT a.studentid AS Studentid, a.groupid AS Groupid, COUNT(*)
FROM table AS a
     JOIN table AS b ON a.groupid = b.groupid
GROUP BY a.studentid, a.groupid

So here we join the table with itself and use a GROUP BY to group on the studentid and groupid and then use COUNT(*) to count the number of rows in b that have the same groupid.

Upvotes: 1

Related Questions