Having troubles with a conditional count in SQL

January 15, 2022

I’m working on an SQL project (involving a library database) and I’m having a hard time figuring out how to make a conditional count.

So, I have 4 tables: Imprumuturi, Cititori, Autori, Carti. I need to list the ‘Cititori’ that have more than one borrowed ‘Carti’ at the current time.

I tried to use

SELECT cititori.nume_cititor,COUNT(imprumuturi.pk_cititor) 
AS numar_imprumuturi FROM cititori, imprumuturi 
WHERE imprumuturi.data_return IS NULL GROUP BY cititori.nume_cititor
HAVING COUNT(imprumuturi.pk_cititor)>1 
ORDER BY cititori.nume_cititor;

And while it lists all the ‘Cititori", it doesn’t count the number of active borrowed ‘Carti’ as it should.

Can I get a hint or some help on how to make it work?

These are the fields of my database

>Solution :

seems you missed the relation between the tables

SELECT cititori.nume_cititor,COUNT(imprumuturi.pk_cititor) 
AS numar_imprumuturi 
FROM cititori
INNER JOIN imprumuturi ON imprumuturi.pk_cititori = cititori.pk_cititori
WHERE imprumuturi.data_return IS NULL 
GROUP BY cititori.nume_cititor
HAVING COUNT(imprumuturi.pk_cititor)>1 
ORDER BY cititori.nume_cititor;

As suggestion, You sould not use the old implicit join sintax based on comma separated table’s name and where condition but use explicit join sintax