CODEWITHSUNDEEP
mysqlsql
JWingert
JWingert

Reputation: 111

Trouble Understanding Joining Tables SQL

Please see attached Tables image.

The question I have:

Find the top 5 occupations that borrowed the most in 2016

The code I have:

select c.occupation, count(*) no_mostborrow
from client c
Inner Join client c on c.clientID = b.clientID
where b.borrowDate >= '2016-01-01' and b.borrowDate < '2017-01-01'
group by c.clientoccupation, c.clientid
order by count(*) asc
limit 5

I feel like I am missing something here but I am not sure what. I am sure I am completely off. Thank you so much for your time.

enter image description here

Upvotes: 2

Views: 60

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269583

To answer your question, you only want occupation in the group by. And the join needs to be correct:

select c.occupation, count(*) as no_mostborrow
from client c join
     borrower b
     on c.clientid = b.clientid
where b.borrowDate >= '2016-01-01' and b.borrowDate < '2017-01-01'
group by c.clientoccupation
order by count(*) asc
limit 5

Upvotes: 1

Related Questions