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_number
column from themeter
table. -
JOIN electricity_reading ON meter.id = electricity_reading.meter_id
: This part of the query joins themeter
table with theelectricity_reading
table based on the condition that theid
in themeter
table matches themeter_id
in theelectricity_reading
table. -
WHERE TIME(electricity_reading.reading_time) = '20:00:00'
: This part of the query filters the records to only include those where thereading_time
is 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_units
for eachmeter_number
. -
LIMIT 1
: This part of the query limits the results to the top 1 record, which will be themeter_number
with the maximumtotal_units
at 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.