SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

/*
  Set up 3 categories
   - category_a
   - category_b
   - category_c
  
  Set up loggers & groups
   - '14d8e2c0-3b63-4614-82a4-1311410e427e' = logger1
   - 'cb21aee4-1a1d-4edc-a113-a2f715c031b1' = logger2, logger3
   - '4670b24d-1057-43ed-9adc-880c3b424df3' = logger4, logger6
   - '0033db52-5285-472c-a589-ec3f19c63ebb' = logger5, logger7
   
  Record logger data
   - logger1-4 under category_a
   - logger5-6 under category_b
   - logger7 under category_c
   
  Should yield the following results
   - category_a = 3 groups
   - category_b = 2 groups
   - category_c = 1 group

CREATE TABLE categories
(
    id UNIQUEIDENTIFIER PRIMARY KEY,
    name NVARCHAR(50)
);

CREATE TABLE [group]
(
    id UNIQUEIDENTIFIER PRIMARY KEY
);

CREATE TABLE logger
(
    id UNIQUEIDENTIFIER PRIMARY KEY,
    group_id UNIQUEIDENTIFIER,
    uuid CHAR(17)
);

CREATE TABLE data
(
    id UNIQUEIDENTIFIER PRIMARY KEY,
    logger_uuid CHAR(17),
    category_name NVARCHAR(50)
);

INSERT INTO categories (id, name) VALUES ('974b0fee-66eb-49f8-ae7f-2cddd0eb7571', 'category_a');
INSERT INTO categories (id, name) VALUES ('30f4dec8-8cc5-42c2-9f6d-9a790ab26ae1', 'category_b');
INSERT INTO categories (id, name) VALUES ('aa7b1395-7def-4e6c-afe0-84468cb51172', 'category_c');

INSERT INTO [group] (id) VALUES ('14d8e2c0-3b63-4614-82a4-1311410e427e');
INSERT INTO [group] (id) VALUES ('cb21aee4-1a1d-4edc-a113-a2f715c031b1');
INSERT INTO [group] (id) VALUES ('4670b24d-1057-43ed-9adc-880c3b424df3');
INSERT INTO [group] (id) VALUES ('0033db52-5285-472c-a589-ec3f19c63ebb');

INSERT INTO logger (id, group_id, uuid) VALUES ('a93c974a-d512-4a26-8c0e-4b51f473211c', '14d8e2c0-3b63-4614-82a4-1311410e427e', 'logger1');
INSERT INTO logger (id, group_id, uuid) VALUES ('6146f1ba-a880-4cfd-b6ff-7bf2c8d181ef', 'cb21aee4-1a1d-4edc-a113-a2f715c031b1', 'logger2');
INSERT INTO logger (id, group_id, uuid) VALUES ('04a000d6-985f-41e4-b4bc-97371920c1f4', 'cb21aee4-1a1d-4edc-a113-a2f715c031b1', 'logger3');
INSERT INTO logger (id, group_id, uuid) VALUES ('3c971a32-5c88-466b-94de-e1310fb71c38', '4670b24d-1057-43ed-9adc-880c3b424df3', 'logger4');
INSERT INTO logger (id, group_id, uuid) VALUES ('0cfabc8e-711a-4f4d-8439-1f9f0ef6fee4', '0033db52-5285-472c-a589-ec3f19c63ebb', 'logger5');
INSERT INTO logger (id, group_id, uuid) VALUES ('8aa0e4d6-3dce-4127-9bbd-d2b041cb1bb0', '4670b24d-1057-43ed-9adc-880c3b424df3', 'logger6');
INSERT INTO logger (id, group_id, uuid) VALUES ('300aadbc-7237-4033-b0e4-349c2850ffca', '0033db52-5285-472c-a589-ec3f19c63ebb', 'logger7');

INSERT INTO data (id, logger_uuid, category_name) VALUES ('70e34017-3a16-4524-b72d-30048ba306c5', 'logger1', 'category_a');
INSERT INTO data (id, logger_uuid, category_name) VALUES ('e9fd273b-a87a-46f8-8e2c-7f305cbe86d1', 'logger2', 'category_a');
INSERT INTO data (id, logger_uuid, category_name) VALUES ('c1a4bd24-7ee6-4bd8-889b-b71f15129c53', 'logger3', 'category_a');
INSERT INTO data (id, logger_uuid, category_name) VALUES ('d95561d2-02e0-42f1-bed6-aba5006bdb9d', 'logger4', 'category_a');
INSERT INTO data (id, logger_uuid, category_name) VALUES ('ad09b542-252f-4234-85a2-3e0c5d7f2920', 'logger1', 'category_a');
INSERT INTO data (id, logger_uuid, category_name) VALUES ('cd2b3508-47e4-4fc4-b01d-0a565da728db', 'logger5', 'category_b');
INSERT INTO data (id, logger_uuid, category_name) VALUES ('f20b0140-68f4-4455-8d1d-14f9cae04123', 'logger5', 'category_b');
INSERT INTO data (id, logger_uuid, category_name) VALUES ('bcee99ad-509c-436c-b720-ff73755c82c3', 'logger6', 'category_b');
INSERT INTO data (id, logger_uuid, category_name) VALUES ('e9395838-02a8-4e74-99b0-9a02414b265d', 'logger6', 'category_b');
INSERT INTO data (id, logger_uuid, category_name) VALUES ('fac4543e-5eca-4748-adf2-bffa50fa8b99', 'logger7', 'category_c');
INSERT INTO data (id, logger_uuid, category_name) VALUES ('7b5a9ed3-1d2b-42af-be2c-2cec4437e4f6', 'logger7', 'category_c');
INSERT INTO data (id, logger_uuid, category_name) VALUES ('07d73818-e68e-4504-9f5a-fbde98f5ceb2', 'logger5', 'category_c');

SELECT g.id as category_id, COUNT(DISTINCT(a.id)) AS group_count 
FROM categories g
  LEFT OUTER JOIN data d ON d.category_name = g.name
  INNER JOIN logger s ON s.uuid = d.logger_uuid
  INNER JOIN [group] a ON a.id = s.group_id
GROUP BY g.id

category_id	group_count
974b0fee-66eb-49f8-ae7f-2cddd0eb7571	3
aa7b1395-7def-4e6c-afe0-84468cb51172	1
30f4dec8-8cc5-42c2-9f6d-9a790ab26ae1	2