How can I select multiple count() rows in SQLite?

Question

I'm working on a database to keep track of packages I need for a personal project. I am also treating this as an exercise to teach myself database design and SQL. The database I am using has a schema like the following:

CREATE TABLE packages
(
    ID INTEGER PRIMARY KEY,
    Name TEXT UNIQUE ON CONFLICT REPLACE NOT NULL ON CONFLICT IGNORE
);
CREATE TABLE dependencies 
(
    dependentPackage INTEGER REFERENCES pages(ID),
    requiredPackage INTEGER REFERENCES pages(ID)
);

where the package referenced by dependencies.dependentPackage depends on the package referenced by dependencies.requiredPackage I want a query with a column NumPackagesRequired, which returns a table that looks something like this:

packageName | NumDependencies
package1 | 6
package5 | 8
package9 | 1

I cannot achieve this by trying:

SELECT p.name AS packageName, count (d.requiredPackage) AS numDependencies
FROM packages p
JOIN dependencies d ON d.dependentPackage=p.ID;

because it returns only one row, containing the first package's name and the count of all the requirements. I tried nesting a SELECT statement as a parameter to the count() function, but I still only got a single row of results. I have searched the sqlite documentation with no degree of luck.

How can I get a table like the one expected above?

I think you are looking for GROUP BY

user180100
– user180100

2016-09-21 16:36:25 +00:00
Commented Sep 21, 2016 at 16:36 — user180100
– user180100, Commented Sep 21, 2016 at 16:36

CL. · Accepted Answer · 2016-09-21 17:18:11Z

1

When you are using GROUP BY, aggregate functions are computed over each group:

SELECT p.name AS packageName,
       count (d.requiredPackage) AS numDependencies
FROM packages p
JOIN dependencies d ON d.dependentPackage=p.ID
GROUP BY p.name;

Alternatively, move the couting into a correlated subquery:

SELECT name AS packageName,
       (SELECT count(*)
        FROM dependencies
        WHERE dependentPackage = packages.ID
       ) AS numDependencies
FROM packages;

answered Sep 21, 2016 at 17:18

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How can I select multiple count() rows in SQLite?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related