Selecting a column from a table and the Count() of a column in another table-CodePudding

Consider the following tables:

   TABLE PAPER // it contains IDs of scientific papers and IDs of the scientists who wrote them
╔═══════════╦═════════════╗
║ PaperID   ║ ScientistID ║
╠═══════════╬═════════════╣
║ 10        ║ 1           ║
╠═══════════╬═════════════╣
║ 11        ║ 1           ║
╠═══════════╬═════════════╣
║ 12        ║ 2           ║
╠═══════════╬═════════════╣
║ 13        ║ 3           ║
╚═══════════╩═════════════╝


   TABLE SCIENTISTS // it contains IDs of scientists and their names
╔═════════════╦════════════════╗
║ ScientistID ║ ScientistName  ║
╠═════════════╬════════════════╣
║ 1           ║ Tikola Nesla   ║
╠═════════════╬════════════════╣
║ 2           ║ Carie Murie    ║
╠═════════════╬════════════════╣
║ 3           ║ Nsaac Iewton   ║
╚═════════════╩════════════════╝

I'm looking for a SELECT query that prints names of scientists and how many times they released a scientific paper, the desired result should be this:

CountOfPapersPerScientist     ScientistName
-------------------------     -------------
                        2      Tikola Nesla 
                        1       Carie Murie
                        1      Nsaac Iewton

I was able to print just the count of papers per scientist using this:

SELECT COUNT(PaperID) FROM PAPER GROUP BY ScientistID;

But this doesn't work:

SELECT COUNT(PaperID), ScientistName FROM PAPER, SCIENTISTS WHERE SCIENTISTS.ScientistID=PAPER.ScientistID GROUP BY PAPER.ScientistID;

ERROR at line 1:
ORA-00979: not a GROUP BY expression

Can you please explain what is a "GROUP BY expression" and why my query isn't one? and please suggest an alternative query, Thanks!

CodePudding user response：

here is what you need , simple group by and join :

select s.ScientistName, count(p.paperId) CountOfPapersPerScientist
from paper p
join SCIENTISTS s on p.ScientistID = s.ScientistID
group by s.ScientistName