I am having trouble displaying the necessary data. A few authors are omitted since they have not published a book

Question

I am trying to show all authors and how many books they have published, however the below query omits the authors with no books published

SELECT INITCAP(fname), INITCAP(lname), COUNT(*) FROM author JOIN bookauthor USING (authorid) GROUP BY INITCAP(fname), INITCAP(lname);

score 2 · Accepted Answer · answered Oct 03 '18 at 16:40

2

When you use aggregate function you need to add non-aggregate colnums in group by

So add group by on INITCAP(fname), INITCAP(lname)

SELECT INITCAP(fname), INITCAP(lname), COUNT(*)
FROM author 
JOIN bookauthor USING (authorid)
GROUP BY INITCAP(fname), INITCAP(lname)

answered Oct 03 '18 at 16:40

D-Shih

do you know why this query would be excluding names that have a null value in the field being counted? – ReeDfence Oct 03 '18 at 16:58

1 Answers1