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.
