CODEWITHSUNDEEP
sqlpostgresql
Noob
Noob

Reputation: 2807

Why does group by statement do not return correct rows?

I'm going through a tutorial and I can't understand why my query is not working.

I have 2 tables :

authors

id name
1 JK Rowling
2 Stephen King
3 Agatha Christie
4 Dr Seuss

books

id title author_id
1 Chamber of Secrets 1
2 Prizoner of Azkaban 1
3 The Dark Tower 2
4 Murder at the Links 3
5 Affait at Styles 3
6 Cat in the hat 4

The task was to write a query that would print an author's name and the number of books they have authored.

My solution was this one:

SELECT name, COUNT(*)   
FROM books
JOIN authors ON books.author_id = authors.id
GROUP BY author_id

However, in the result there is no name row. I know that the correct solution is to GROUP BY name, however I can't understand why I can't group by author_id? Thanks for your help.

Upvotes: 1

Views: 293

Answers (2)

Teson
Teson

Reputation: 6736

Put simply, any column not given a aggregate function needs to be present in the group by statement.

In your case

select name, ..

requires

group by name, ...

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270783

First, you should write your query with qualified column references so it is easier to follow:

SELECT a.name, COUNT(*)   
FROM books b JOIN
     authors a
     ON b.author_id = a.id
GROUP BY b.author_id;

Why doesn't this work? Clearly the SELECT and GROUP BY columns are different! They are not even from the same table.

The following might work:

SELECT a.name, COUNT(*)   
FROM books b JOIN
     authors a
     ON b.author_id = a.id
GROUP BY a.id;

This would work if authors.id is declared to be the primary key or unique -- which is quite likely. This works because of a capability called functional dependence, which alls for any column to be referenced when a unique or primary key is in a GROUP BY key.

However, if there is no such explicit declaration, then you need a.name in the GROUP BY clause.

Upvotes: 2

Related Questions