Having troble trying to compare count from two tables (using SQLite).
Supposing I have tables like:
Objects (NumId is Primary key)
| NumId | Object_category |
|---|---|
| 1 | permanent |
| 2 | borrowed |
| 3 | borrowed |
| 4 | borrowed |
| 5 | borrowed |
Borrowed (NumObj is Foreign Key/Primary Key referring Object table)
| NumObj | Collection_name |
|---|---|
| 2 | collection_alpha |
| 3 | collection_betha |
| 4 | collection_betha |
| 5 | collection_betha |
Query I need to perform is:
"What are the collections with higher number of borrowed objects than quantity of permanent objets?"
My current attempt is:
SELECT Collection_name FROM Borrowed
WHERE (SELECT COUNT(Collection_name) FROM Borrowed)>
(SELECT COUNT(*) FROM Objects WHERE Object_category =
'permanent')
It is returning the values from the column, not working as supposed (hould have returned the 'collection_betha' for owning 3 objects, greater than number of existing permanent objects that is 1).
CodePudding user response：
I would use conditional aggregation here:
SELECT b.Collection_name
FROM Borrowed b
INNER JOIN Objects o
ON o.NumId = b.NumObj
GROUP BY b.Collection_name
HAVING SUM(o.Object_category = 'borrowed') > SUM(o.Object_category = 'permanent');
CodePudding user response：
Maybe a join will help
SELECT Borrowed.Collection_name
FROM Borrowed
JOIN Objects
ON Objects.NumId= Borrowed.NumObj
GROUP BY Borrowed.Collection_name
HAVING COUNT(NumObj) >
(SELECT COUNT(*) FROM Objects WHERE Object_category = 'permanent')