CODEWITHSUNDEEP
sqlpostgresql
Pa2k3l1s
Pa2k3l1s

Reputation: 1

Get the year with the greatest count of rows

Note, I just started learning PostgreSQL for university and English is not my mother tongue.

I need to find the year at which released books are the most popular (year of released books when the most copies were taken).

Here I get three years at which books were released and the number of copies taken:

Select distinct book.year, count(copy.nr)
From stud.book, stud.copy
Where copy.taken is not null and book.isbn=copy.isbn
Group by book.year;

This is what I get:

 year | count
------+-------
 2007 |     2
 2006 |     9
 2005 |     5
 (3 rows)

And this is what I really need:

 year | count
------+-------
 2006 |     9

Because books released in 2006 are the most popular (9 copies were taken of books released in 2006).

Upvotes: 0

Views: 49

Answers (2)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You should use appropriate join syntax instead of declaring the join conditions in the where clause.

One approach to do this is using a cte.

with counts as (
select book.year, count(copy.nr) as cnt
from stud.book join stud.copy
on book.isbn=copy.isbn
where copy.taken is not null
group by book.year ) 
select * from counts
where cnt = (select max(cnt) as maxcnt from counts)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270001

Use order by and limit 1:

Select book.year, count(copy.nr) as cnt
From stud.book, stud.copy
Where copy.taken is not null and book.isbn=copy.isbn
Group by book.year
Order by cnt desc
Limit 1;

Note: Don't use distinct with group by, unless you really know what you are doing. Also, table aliases and explicit join syntax make such a query easier to write and to read:

Select b.year, count(c.nr) as cnt
From stud.book b join
     stud.copy c
     on b.isbn = c.isbn
Where c.taken is not null
Group by b.year
Order by cnt desc
Limit 1;

A simple rule: Never use commas in the from clause; always use explicit join syntax.

Upvotes: 2

Related Questions