Write a query to display the meter_number ,total_units, payable_amount and hourly usage of by morning, afternoon, evening and night for the month December 2017 of each meter. Display the record in descending order by total_units.Note:1. Display only the below 6 columns, i. meter_number ii. total_units iii. payable_amount iv. hourly usage by morning ( consider 6 to 11 as Morning ) v. hourly usage by afternoon ( consider 12 to 15 as afternoon ) vi. hourly usage by evening ( consider 16 to 19 as evening ) and vii. Remaining as hourly usage by night.2. Use Joins3. Tables involved are electricity_reading, meter and bill4. Order by is mandatory.5. Compare your executed result with the sample output given below.
Question
Write a query to display the meter_number ,total_units, payable_amount and hourly usage of by morning, afternoon, evening and night for the month December 2017 of each meter. Display the record in descending order by total_units.Note:1. Display only the below 6 columns, i. meter_number ii. total_units iii. payable_amount iv. hourly usage by morning ( consider 6 to 11 as Morning ) v. hourly usage by afternoon ( consider 12 to 15 as afternoon ) vi. hourly usage by evening ( consider 16 to 19 as evening ) and vii. Remaining as hourly usage by night.2. Use Joins3. Tables involved are electricity_reading, meter and bill4. Order by is mandatory.5. Compare your executed result with the sample output given below.
Solution
The language of the text is SQL (Structured Query Language). Here is the SQL query that you can use to get the desired output:
SELECT
m.meter_number,
SUM(e.total_units) AS total_units,
b.payable_amount,
SUM(CASE WHEN HOUR(e.reading_time) BETWEEN 6 AND 11 THEN e.total_units ELSE 0 END) AS morning_usage,
SUM(CASE WHEN HOUR(e.reading_time) BETWEEN 12 AND 15 THEN e.total_units ELSE 0 END) AS afternoon_usage,
SUM(CASE WHEN HOUR(e.reading_time) BETWEEN 16 AND 19 THEN e.total_units ELSE 0 END) AS evening_usage,
SUM(CASE WHEN HOUR(e.reading_time) NOT BETWEEN 6 AND 19 THEN e.total_units ELSE 0 END) AS night_usage
FROM
electricity_reading e
JOIN
meter m ON e.meter_id = m.id
JOIN
bill b ON b.meter_id = m.id
WHERE
MONTH(e.reading_time) = 12 AND YEAR(e.reading_time) = 2017
GROUP BY
m.meter_number, b.payable_amount
ORDER BY
total_units DESC;
This query first joins the electricity_reading, meter, and bill tables using the meter_id field. It then filters the records for the month of December 2017. The SUM function with CASE statements is used to calculate the total units used in the morning, afternoon, evening, and night. Finally, the results are ordered in descending order by total_units.
Similar Questions
Write a query to display owner name,address ,meter number and total units consumed by all the customers in the month december 2017.Display the records in descending order by total_units.Note:1. Display only the below 4 columns, i. owner name ii. address iii. meter number iv. Total unitsby all the customers in the month december 2017.2. Use Joins3. Tables involved building, meter and bill4. Specify condition on year, month column of bill table5. Order by is mandatory.
Write a query to display the electricity readings for the meters with meter number 'SG923564' and 'SG288942' in descending order based on the total units consumed per day.
Write a query to display the meter_id and total_units of electricity_reading whose '13th' hour reading is lesser than the '14th' hour reading, Display the records in descending order based on their total_units.
Write a query to display all the details of the 'electricity_reading' whose 'total_units' per day is between 500 and 1000.Display the records in ascending order based on their total_units.
Write a query to display the meter_number from the meter table which got minimum amount of total_units in a month. Display the records in ascending order based on their meter_number.Note:1. Display only the meter_number from the meter table which got minimum amount of total_units in a month.2. Tables involved are meter and bill3. Order by is mandatory4. Compare your executed result with the sample output given below.Sample output:
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.