States where the number of suppliers exceeds the number of consumers

I’m working on trying to get states where the number of suppliers is greater than the number of consumers in those states. Here is the code I’m tryin to get working: When I exclude the having clause I get the following results:

The code is giving me no results. Also here is a snapshot of the database diagram.

Answer

Use count(distinct) instead of count():

select s.state, count(distinct s.supp_id), count(distinct c.con_id)

What is happening is that you are getting a Cartesian product within each state of every supplied crossed with every consumer.

A better solution is actually to aggregate the data before joining. But this is the simplest change to the query that you have written.