I have three Tables:
CREATE TABLE IF NOT EXISTS countable(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS collection(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS count(
countableId INTEGER NOT NULL,
collectionId INTEGER NOT NULL,
count INTEGER NOT NULL
);
My plan is to be able to select two collections (For example collection.id = 3 OR collection.id = 4) and get two counts per countable. This already works:
+-----------+------------+-------+
| countable | collection | count |
+-----------+------------+-------+
| Example | Col 3 | 0 |
| Example | Col 4 | 5 |
| Test | Col 3 | 1 |
| Test | Col 4 | 0 |
| Foo | Col 3 | 0 |
| Foo | Col 4 | 0 |
| Bar | Col 3 | 5 |
| Bar | Col 4 | 3 |
+-----------+------------+-------+
However, I want a table where there are no duplicate coutables, something like this:
+-----------+--------------+----------------+
| countable | primaryCount | secondaryCount |
+-----------+--------------+----------------+
| Example | 0 | 5 |
| Test | 1 | 0 |
| Foo | 0 | 0 |
| Bar | 5 | 3 |
+-----------+--------------+----------------+
I tried using grouping, which sort of works:
SELECT
countable.name AS countable,
count AS primaryCount, <--- This is the Problem
count AS secondaryCount <--- This is the Problem
FROM count
JOIN countable ON countable.id = count.countableId
JOIN collection ON collection.id = count.collectionId
WHERE collection.id = 3 OR collection.id = 4
GROUP BY countable.id
;
But this just returns the primatyCount both times:
+-----------+--------------+----------------+
| countable | primaryCount | secondaryCount |
+-----------+--------------+----------------+
| Example | 0 | 0 |
| Test | 1 | 1 |
| Foo | 0 | 0 |
| Bar | 5 | 5 |
+-----------+--------------+----------------+
I also tried group_concat like this:
SELECT
countable.name AS countable,
group_concat(count)
FROM count
JOIN countable ON countable.id = count.countableId
JOIN collection ON collection.id = count.collectionId
WHERE collection.id = 3 OR collection.id = 4
GROUP BY countable.id
;
This returns:
+-----------+---------------------+
| countable | group_concat(count) |
+-----------+---------------------+
| Example | 0,5 |
| Test | 1,0 |
| Foo | 0,0 |
| Bar | 5,3 |
+-----------+---------------------+
which works, however I would have to split the values in code, which is not really ideal I guess. Is there some syntax like count[0] and count[1] or other solutions to do this cleanly in SQLite?
Here is some sample data:
INSERT INTO countable VALUES (1, "Example");
INSERT INTO countable VALUES (2, "Test");
INSERT INTO countable VALUES (3, "Foo");
INSERT INTO countable VALUES (4, "Bar");
INSERT INTO collection VALUES (1, "Col 1");
INSERT INTO collection VALUES (2, "Col 2");
INSERT INTO collection VALUES (3, "Col 3");
INSERT INTO collection VALUES (4, "Col 4");
INSERT INTO count VALUES (1, 1, 4);
INSERT INTO count VALUES (1, 2, 2);
INSERT INTO count VALUES (1, 3, 0);
INSERT INTO count VALUES (1, 4, 5);
INSERT INTO count VALUES (2, 1, 0);
INSERT INTO count VALUES (2, 2, 0);
INSERT INTO count VALUES (2, 3, 0);
INSERT INTO count VALUES (2, 4, 0);
INSERT INTO count VALUES (3, 1, 0);
INSERT INTO count VALUES (3, 2, 0);
INSERT INTO count VALUES (3, 3, 0);
INSERT INTO count VALUES (3, 4, 0);
INSERT INTO count VALUES (4, 1, 2);
INSERT INTO count VALUES (4, 2, 5);
INSERT INTO count VALUES (4, 3, 5);
INSERT INTO count VALUES (4, 4, 3);
You seem to just want conditional aggregation here:
SELECT
cnt.name AS countable,
SUM(CASE WHEN col.id = 3 THEN c.count ELSE 0 END) AS primaryCount,
SUM(CASE WHEN col.id = 4 THEN c.count ELSE 0 END) AS secondaryCount
FROM count c
INNER JOIN countable cnt
ON cnt.id = c.countableId
INNER JOIN collection col ON col.id = c.collectionId
GROUP BY cnt.name;
The idea is to aggregate by the countable name and for each group of records keep conditional tallies of the number of records belonging to collections 3 and 4.