Performing a count of column from another column in MYSQL

Question

I have a table with two columns meetingID and caseID. The meetingIDs can be from one of three categories, so I use a subquery to select a particular one by returning only the meetingIDs from another table that match that category.

Once I have this list of meetingIDs I want to count how many of them have two caseIDs. I also need to exclude a few caseIDs from the count, but that's irrelevant for now.

Here's the code I have so far;

 SELECT COUNT( meetingID ) , meetingID FROM case_meeting WHERE meetingID IN ( SELECT DISTINCT a.meetingID FROM case_meeting a INNER JOIN meeting b ON a.meetingID = b.meetingID WHERE b.categoryID = '1' ) GROUP BY meetingID HAVING COUNT( caseID ) =2

The main problem I have is actually writing code that will successfully count the number of meetingIDs that have two caseIDs instead of returning a list of them.

at the moment the code is returning a list where the number of rows matches the figure i want and in each row is a '2'

Looks like you're missing a closing ')' somewhere. Also, can you provide some sample data? — Noam Kremen
– Noam Kremen, Commented Jan 17, 2013 at 10:14
The 2 is the value of the COUNT() or meetingID? The query seems to be logically and syntactically correct. — hjpotter92
– hjpotter92, Commented Jan 17, 2013 at 10:24
yeh 2 is the count I want to make. how many meetingIDs have 2 caseIDs. This part is working. I just to need to count the rows from this query. I'm just not sure how to make a count of this query. — blarg
– blarg, Commented Jan 17, 2013 at 10:27
I can't do a sum of these rows because they are displaying 2 so it would give a figure of double what i want — blarg
– blarg, Commented Jan 17, 2013 at 10:29
Well, if your current query returns a list and all you want is to count the number of rows in the list, just put it inside another subquery and do a count(*). — Noam Kremen
– Noam Kremen, Commented Jan 17, 2013 at 10:49

ypercubeᵀᴹ · Accepted Answer · 2013-01-17 15:15:53Z

If there is a UNIQUE constraint on (meetingID, caseID):

SELECT cm.meetingID FROM case_meeting AS cm INNER JOIN meeting AS m ON m.meetingID = cm.meetingID WHERE m.categoryID = 1 GROUP BY cm.meetingID HAVING COUNT(*) = 2 ;

If there isn't, change the last line to:

HAVING COUNT(DISTINCT cm.caseID) = 2 ;

Collectives™ on Stack Overflow

Performing a count of column from another column in MYSQL

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related