kerv kerv - 3 months ago 9
MySQL Question

How do I get count of a row in SQL

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
89 10
95 10
83 11


The result should look something like this:

MID jachtID count
89 10 2
95 10 2
83 11 1


And I need this for all of the rows
I have tried using

SELECT count(DISTINCT jachtID) FROM table


But this just gives me 1 big number and not the result that I need.

Any help would be appreciated.

Answer

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 

First get 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.