I am a beginner regarding SQL queries so I hope that someone can help me with this.
I have a table that has 2 columns that are called MID and jachtID.
What I need is the count of how many of the same jachtIDS there are with different MIDS attached to them like this:
MID jachtID count
89 10 2
95 10 2
83 11 1
SELECT count(DISTINCT jachtID) FROM table
You can try the following query:
SELECT T.MID, T.jachtID, jT.total FROM table T INNER JOIN ( SELECT jachtID, COUNT(*) total FROM table GROUP BY jachtID )AS jT ON T.jachtID = jT.jachtID
count of each
jachtID by the following query:
SELECT jachtID, COUNT(*) total FROM table GROUP BY jachtID
Then make an
INNER JOIN between the main table and the above query and get the corresponding
jatchtID count thereby.