sql query COUNT number of occurences when joining a table-CodePudding

Im trying to return the names of all members that have registered a tasting of a coffe in my DB, and count number of tasting each name has left.

select bruker.navn, COUNT(*) from kaffesmaking inner join bruker on kaffesmaking.bruker_epost = bruker.epost

table "user" has the columns

name, email , password

table "tasting (alias kaffesmaking) has the columns:

id, note, date, score, user_email, coffename

I want a list displaying

names with numbers of tastings done by that particular user.

i.e

John Johnson 5 Jessie ashford 3 Paul newman 8

Where the number represents the number of rows that specific user has added in the tasting table.

Any help would be appreciated

CodePudding user response：

You were almost there, you need to add a "group by" to your SQL

SELECT bruker.navn
     , COUNT(*) as tastings
  FROM kaffesmaking
  INNER JOIN bruker
       ON kaffesmaking.bruker_epost = bruker.epost;
  group by bruker.navn;