Counting the number of times a result from a query shows up (basic SQL question)

I have these three tables:

enter image description here

I need to list staff names and the number of projects they have worked on, I'm not really sure how to do this. Right now I'm able to list all the staff members who have worked on a project using this:

SELECT staff.s_name FROM Staff staff
INNER JOIN Work_on work ON staff.s_id = work.s_id
INNER JOIN Projects proj ON work.p_id = proj.p_id

And this displays the names of all the people who have worked on a project, some names are listed twice, indicating how many projects they've worked on. I don't know how to list this as a separate value though and display both their names and the number of projects they've been working on, obviously I'd need to use the COUNT() function somewhere but I don't know how to count everytime a name appears from the result. Could someone provide me a tip of sorts?

The result is supposed to look like this:

enter image description here

Right now I'm only getting this:

enter image description here

Which is correctly displaying how many times a user has worked on a project, but I don't know how to use this to create another column displaying the count.

1 answer

  • answered 2021-02-22 22:40 ekochergin

    Would it help?

    SELECT staff.s_name, count(1) FROM Staff staff
    JOIN Work_on work ON staff.s_id = work.s_id
    GROUP BY staff.s_name