Write a query to display the meter_number from the meter table which got maximum total_units during 8PM for a day in the electricity_reading.
Question
Write a query to display the meter_number from the meter table which got maximum total_units during 8PM for a day in the electricity_reading.
Solution
Sure, here is a SQL query that should give you the desired result:
SELECT meter_number
FROM meter
JOIN electricity_reading
ON meter.id = electricity_reading.meter_id
WHERE TIME(electricity_reading.reading_time) = '20:00:00'
GROUP BY meter_number
ORDER BY SUM(electricity_reading.total_units) DESC
LIMIT 1;
Here's a step-by-step explanation of the query:
-
SELECT meter_number FROM meter: This part of the query selects themeter_numbercolumn from themetertable. -
JOIN electricity_reading ON meter.id = electricity_reading.meter_id: This part of the query joins themetertable with theelectricity_readingtable based on the condition that theidin themetertable matches themeter_idin theelectricity_readingtable. -
WHERE TIME(electricity_reading.reading_time) = '20:00:00': This part of the query filters the records to only include those where thereading_timeis 8PM. -
GROUP BY meter_number: This part of the query groups the results bymeter_number. -
ORDER BY SUM(electricity_reading.total_units) DESC: This part of the query orders the results in descending order based on the sum oftotal_unitsfor eachmeter_number. -
LIMIT 1: This part of the query limits the results to the top 1 record, which will be themeter_numberwith the maximumtotal_unitsat 8PM.
Similar Questions
Write a query to display the number of electricity_reading in which the total_units per day is greater than 500. Give an alias name as 'count_total_units_500'.
Write a query to display unique 12th hour reading present in the electricity_reading table. Display the records sorted in descending order based on h12
Write a query to display the total_units and payable_amount of the particular meter number 'SG934826
Write a query to display all the details of the 'meter' whose meter_number starts with 'S' and ends with '6'.
Write a query to display the total_units, payable_amount and fine_amount of bill table. Display the records in descending order based on their total_units
Upgrade your grade with Knowee
Get personalized homework help. Review tough concepts in more detail, or go deeper into your topic by exploring other relevant questions.