clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2591601 fiddles created (45735 in the last week).

/* 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
 hidden batch(es)