CODEWITHSUNDEEP
sqlsqlite
MightyPork
MightyPork

Reputation: 18881

Count rows from another table referencing current row by foreign key

I have tables as follows:

inverter [ id, address, ... ]
string [ id, inverter_id (foreign key), ... ]

I want to select all "inverters", together with the number of "strings" attached to them.

I tried this query here, but it gives me empty result, so how can I do this?

SELECT inverter.*, COUNT(string.*) as string_count
FROM inverter 
LEFT JOIN string ON string.inverter_id = inverter.id
ORDER BY address

I am using SQLite3.

Here's a dump of the test tables I have now:

CREATE TABLE `inverter` (`id` INTEGER NULL PRIMARY KEY AUTOINCREMENT, `address` VARCHAR(3) NULL, `comment` VARCHAR(250) NULL);

INSERT INTO "inverter" ("id","address","comment") VALUES ('2','A1','North side');
INSERT INTO "inverter" ("id","address","comment") VALUES ('3','A2','Broken inverter');
INSERT INTO "inverter" ("id","address","comment") VALUES ('4','A3','');
INSERT INTO "inverter" ("id","address","comment") VALUES ('5','A4','South-west corner');


CREATE TABLE `string` (`id` INTEGER NULL PRIMARY KEY AUTOINCREMENT, `address` VARCHAR(3) NULL, `inverter_id` INTEGER NULL, `comment` VARCHAR(250) NULL, FOREIGN KEY (`inverter_id`) REFERENCES `inverters` (`id`) ON DELETE SET NULL);

INSERT INTO "string" ("id","address","inverter_id","comment") VALUES ('1','XX','3','');
INSERT INTO "string" ("id","address","inverter_id","comment") VALUES ('2','XY','3','Ahoj jak se máš');
INSERT INTO "string" ("id","address","inverter_id","comment") VALUES ('3','XZ','4','Moo');

Upvotes: 0

Views: 1518

Answers (3)

Guffa
Guffa

Reputation: 700650

Using an aggregate always means that you are grouping, and if you don't specify the grouping it will be a single group containing all records. Group on the fields that you get from the inverter table. Also use a single field in the count instead of string.*:

select
  inverter.id, inverter.address, count(string.id) as string_count
from
  inverter 
  left join string on string.inverter_id = inverter.id
group by
  inverter.id, inverter.address
order by
  inverter.address

Upvotes: 0

jpw
jpw

Reputation: 44901

It seems SQLite3 chokes on count(s.*) so try this instead:

select i.*, count(s.id) 
from inverter i 
left join string s on i.id = s.inverter_id group by i.address;

This gives:

2|A1|North side|0
3|A2|Broken inverter|2
4|A3||1
5|A4|South-west corner|0

Upvotes: 3

Partha Mitra
Partha Mitra

Reputation: 130

Change the table name "string". That is a reserved word.

Upvotes: -2

Related Questions