5.Electricity Consumption (Advanced Aggregate Functions in SQL)
In this video, we dive into SQL techniques for analyzing electricity billing data. You'll learn how to calculate the total electricity consumption, total cost, and average monthly consumption for each household per year. We'll walk you through a practical example using a dataset from an electricity billing system, showcasing how to use SQL aggregation functions like SUM and AVG, and how to organize your results with GROUP BY. Perfect for beginners and those looking to enhance their data analysis skills!
--CREATE TABLE billing_data (
-- bill_id INT PRIMARY KEY,
-- household_id INT,
-- billing_period DATE,
-- consumption_kwh FLOAT,
-- total_cost DECIMAL(10, 2)
--);
INSERT INTO billing_data (bill_id, household_id, billing_period, consumption_kwh, total_cost) VALUES
(1, 101, '2023-01-01', 320.5, 48.08),
(2, 101, '2023-02-01', 280.0, 42.00),
(3, 101, '2023-03-01', 310.5, 46.58),
(4, 101, '2023-04-01', 330.5, 49.58),
(5, 101, '2023-05-01', 310.0, 46.50),
(6, 101, '2023-06-01', 350.0, 52.50),
(7, 101, '2023-07-01', 340.0, 51.00),
(8, 101, '2023-08-01', 360.0, 54.00),
(9, 101, '2023-09-01', 370.0, 55.50),
(10, 101, '2023-10-01', 380.0, 57.00),
(11, 101, '2023-11-01', 390.0, 58.50),
(12, 101, '2023-12-01', 400.0, 60.00),
(13, 102, '2023-01-01', 310.0, 46.50),
(14, 102, '2023-02-01', 320.0, 48.00),
(15, 102, '2023-03-01', 330.0, 49.50),
(16, 102, '2023-04-01', 340.0, 51.00),
(17, 102, '2023-05-01', 350.0, 52.50),
(18, 102, '2023-06-01', 360.0, 54.00),
(19, 102, '2023-07-01', 370.0, 55.50),
(20, 102, '2023-08-01', 380.0, 57.00),
(21, 102, '2023-09-01', 390.0, 58.50),
(22, 102, '2023-10-01', 400.0, 60.00),
(23, 102, '2023-11-01', 410.0, 61.50),
(24, 102, '2023-12-01', 420.0, 63.00),
(25, 103, '2023-01-01', 430.0, 64.50),
(26, 103, '2023-02-01', 440.0, 66.00),
(27, 103, '2023-03-01', 450.0, 67.50),
(28, 103, '2023-04-01', 460.0, 69.00),
(29, 103, '2023-05-01', 470.0, 70.50),
(30, 103, '2023-06-01', 480.0, 72.00),
(31, 103, '2023-07-01', 490.0, 73.50),
(32, 103, '2023-08-01', 500.0, 75.00),
(33, 103, '2023-09-01', 510.0, 76.50),
(34, 103, '2023-10-01', 520.0, 78.00),
(35, 103, '2023-11-01', 530.0, 79.50),
(36, 103, '2023-12-01', 540.0, 81.00),
(37, 101, '2024-01-01', 550.0, 82.50),
(38, 101, '2024-02-01', 560.0, 84.00),
(39, 101, '2024-03-01', 570.0, 85.50),
(40, 101, '2024-04-01', 580.0, 87.00),
(41, 101, '2024-05-01', 590.0, 88.50),
(42, 101, '2024-06-01', 600.0, 90.00),
(43, 101, '2024-07-01', 610.0, 91.50),
(44, 101, '2024-08-01', 620.0, 93.00),
(45, 101, '2024-09-01', 630.0, 94.50),
(46, 101, '2024-10-01', 640.0, 96.00),
(47, 101, '2024-11-01', 650.0, 97.50),
(48, 101, '2024-12-01', 660.0, 99.00),
(49, 102, '2024-01-01', 670.0, 100.50),
(50, 102, '2024-02-01', 680.0, 102.00),
(51, 102, '2024-03-01', 690.0, 103.50),
(52, 102, '2024-04-01', 700.0, 105.00),
(53, 102, '2024-05-01', 710.0, 106.50),
(54, 102, '2024-06-01', 720.0, 108.00),
(55, 102, '2024-07-01', 730.0, 109.50),
(56, 102, '2024-08-01', 740.0, 111.00),
(57, 102, '2024-09-01', 750.0, 112.50),
(58, 102, '2024-10-01', 760.0, 114.00),
(59, 102, '2024-11-01', 770.0, 115.50),
(60, 102, '2024-12-01', 780.0, 117.00),
(61, 103, '2024-01-01', 790.0, 118.50),
(62, 103, '2024-02-01', 800.0, 120.00),
(63, 103, '2024-03-01', 810.0, 121.50),
(64, 103, '2024-04-01', 820.0, 123.00),
(65, 103, '2024-05-01', 830.0, 124.50),
(66, 103, '2024-06-01', 840.0, 126.00),
(67, 103, '2024-07-01', 850.0, 127.50),
(68, 103, '2024-08-01', 700.7, 105.11),
(69, 103, '2024-09-01', 710.7, 106.61),
(70, 103, '2024-10-01', 720.7, 108.11),
(71, 103, '2024-11-01', 730.7, 109.61),
(72, 103, '2024-12-01', 740.7, 111.11),
(73, 101, '2025-01-01', 600.7, 90.11),
(74, 101, '2025-02-01', 610.7, 91.61),
(75, 101, '2025-03-01', 620.7, 93.11),
(76, 101, '2025-04-01', 630.7, 94.61),
(77, 101, '2025-05-01', 640.7, 96.11),
(78, 101, '2025-06-01', 650.7, 97.61),
(79, 101, '2025-07-01', 660.7, 99.11),
(80, 101, '2025-08-01', 670.7, 100.61),
(81, 101, '2025-09-01', 680.7, 102.11),
(82, 101, '2025-10-01', 690.7, 103.61),
(83, 101, '2025-11-01', 700.7, 105.11),
(84, 101, '2025-12-01', 710.7, 106.61),
(85, 102, '2025-01-01', 720.7, 108.11),
(86, 102, '2025-02-01', 730.7, 109.61),
(87, 102, '2025-03-01', 740.7, 111.11),
(88, 102, '2025-04-01', 750.7, 112.61),
(89, 102, '2025-05-01', 760.7, 114.11),
(90, 102, '2025-06-01', 770.7, 115.61),
(91, 102, '2025-07-01', 780.7, 117.11),
(92, 102, '2025-08-01', 790.7, 118.61),
(93, 102, '2025-09-01', 800.7, 120.11),
(94, 102, '2025-10-01', 810.7, 121.61),
(95, 102, '2025-11-01', 820.7, 123.11),
(96, 102, '2025-12-01', 830.7, 124.61),
(97, 103, '2025-01-01', 840.7, 126.11),
(98, 103, '2025-02-01', 850.7, 127.61),
(99, 103, '2025-03-01', 860.7, 129.11),
(100, 103, '2025-04-01', 870.7, 130.61);
#SQLtutorial
#SQLaggregation
#ElectricityBillingSystem
#TotalConsumptionPerYear
#AverageMonthlyConsumption
#HouseholdElectricityUsage
#SQLGROUPBY
#SQLSUMFunction
#SQLAVGFunction
#DataAnalysis
#SQLQueryExample
#SQLDataManipulation