Given the following schema
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), recorded_on DATETIME );
And the following rules
datarecord references a
loggerwill always have a
groupcan have multiple
- I only want to count the most recent data recorded
category_name isn't unique per row, it's just a way to associate a given data record under a category,
id is really just a surrogate key.
What would be the optimum way to achieve the a resultset like
category_id | logger_group_count -------------------------------- 12345 4 67890 2 ..... ...
i.e. count the no. of groups for each category where a logger has recorded data?
As an initial stab I came up with:
SELECT g.id, COUNT(DISTINCT(a.id)) AS logger_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
I want to make sure I'm eeking the most out of the query before I start looking at other things i.e. hardware utilization etc. Azure SQL costs can go up substantially (even though you maybe just need a little more juice off your current tier).