Knowee
Questions
Features
Study Tools

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.

🧐 Not the exact question you are looking for?Go ask a question

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:

  1. SELECT meter_number FROM meter: This part of the query selects the meter_number column from the meter table.

  2. JOIN electricity_reading ON meter.id = electricity_reading.meter_id: This part of the query joins the meter table with the electricity_reading table based on the condition that the id in the meter table matches the meter_id in the electricity_reading table.

  3. WHERE TIME(electricity_reading.reading_time) = '20:00:00': This part of the query filters the records to only include those where the reading_time is 8PM.

  4. GROUP BY meter_number: This part of the query groups the results by meter_number.

  5. ORDER BY SUM(electricity_reading.total_units) DESC: This part of the query orders the results in descending order based on the sum of total_units for each meter_number.

  6. LIMIT 1: This part of the query limits the results to the top 1 record, which will be the meter_number with the maximum total_units at 8PM.

This problem has been solved

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

1/1

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.