SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE Device ( Id int IDENTITY(1, 1) NOT NULL PRIMARY KEY, Name varchar(50) NOT NULL, DeviceTypeId int NOT NULL ) CREATE TABLE MeasuredUsage ( Id int IDENTITY(1, 1) NOT NULL PRIMARY KEY, DeviceId int NOT NULL FOREIGN KEY REFERENCES Device ( Id ), StartTimeUtc datetime NOT NULL, Gallons float NOT NULL ) INSERT INTO Device ( Name, DeviceTypeId ) VALUES ('Pump Room', 1), ('Cooling Tower 1', 2), ('Sports Field', 1), ('Kitchen', 3), ('Cooling Tower 2', 2), ('Water Tower', 1) INSERT INTO MeasuredUsage ( DeviceId, StartTimeUtc, Gallons ) VALUES (1, '2024-02-05 10:00', 123), (1, '2024-02-05 11:00', 75), (1, '2024-02-06 10:00', 88), (1, '2024-02-07 10:00', 32), (1, '2024-02-07 11:00', 12), (2, '2024-02-05 10:00', 23), (2, '2024-02-05 11:00', 34), (2, '2024-02-05 12:00', 55), (2, '2024-02-06 10:00', 77), (2, '2024-02-06 11:00', 86), (3, '2024-02-05 10:00', 31), (3, '2024-02-05 11:00', 44), (3, '2024-02-07 10:00', 68), (3, '2024-02-07 11:00', 32), (3, '2024-02-07 12:00', 13), (4, '2024-02-05 10:00', 44), (4, '2024-02-05 11:00', 53), (4, '2024-02-06 10:00', 79), (4, '2024-02-06 11:00', 80), (5, '2024-02-06 12:00', 756), (5, '2024-02-06 13:00', 59), (5, '2024-02-07 10:00', 33), (5, '2024-02-07 11:00', 34), (6, '2024-02-05 10:00', 64), (6, '2024-02-05 11:00', 46), (6, '2024-02-06 12:00', 596) UPDATE Device SET Name = Name + ' [' + LTRIM(STR(DeviceTypeId)) + ']' SELECT d.Id, d.Name, SUM(u.Gallons) AS TotalGallons FROM Device d INNER JOIN MeasuredUsage u ON d.Id = u.DeviceId GROUP BY d.Id, d.Name ORDER BY SUM(u.Gallons) DESC SELECT d.Id, d.Name, SUM(u.Gallons) AS TotalGallons FROM Device d INNER JOIN MeasuredUsage u ON d.Id = u.DeviceId GROUP BY d.Id, d.Name HAVING SUM(u.Gallons) >= 300 ORDER BY SUM(u.Gallons) DESC