Write a query to display the sum of h20 and average of h20 from the electricity_reading table consumed by the building_type named as 'Commercial' on 8PM. Give an alias name for total as 'total_amount' and average as 'average_amount'.Note:1. Display only the sum of h20 and average of h20 from the electricity_reading table consumed by the building_type named as 'Commercial' on 8PM.2. Tables involved are electricity_reading, meter, building, building_type and electricity_connection_type3. Specify the alias name without any mistake.4. Compare your executed result with the sample output given below.
Question
Write a query to display the sum of h20 and average of h20 from the electricity_reading table consumed by the building_type named as 'Commercial' on 8PM. Give an alias name for total as 'total_amount' and average as 'average_amount'.Note:1. Display only the sum of h20 and average of h20 from the electricity_reading table consumed by the building_type named as 'Commercial' on 8PM.2. Tables involved are electricity_reading, meter, building, building_type and electricity_connection_type3. Specify the alias name without any mistake.4. Compare your executed result with the sample output given below.
Solution
Here is the SQL query that you need:
SELECT
SUM(electricity_reading.h20) AS total_amount,
AVG(electricity_reading.h20) AS average_amount
FROM
electricity_reading
JOIN
meter ON electricity_reading.meter_id = meter.id
JOIN
building ON meter.building_id = building.id
JOIN
building_type ON building.building_type_id = building_type.id
JOIN
electricity_connection_type ON meter.electricity_connection_type_id = electricity_connection_type.id
WHERE
building_type.name = 'Commercial'
AND electricity_reading.time = '20:00:00';
This query works as follows:
- It joins the
electricity_reading,meter,building,building_type, andelectricity_connection_typetables based on their relationships. - It filters the records where the
building_typeis 'Commercial' and the time is '8PM'. - It calculates the sum and average of the
h20column from theelectricity_readingtable for the filtered records. - It gives alias names 'total_amount' for the sum and 'average_amount' for the average.
Similar Questions
Write a query to display the average of payable_amount from the bill table for the buildings with electricity connection slab rate less than 24. Give an alias name for payable amount as 'payable_amount'.Note:1. Display only the average of payable_amount from the bill table for the buildings with electricity connection slab rate less than 24.2. Tables involves are bill, meter, building, building_type, electricity_connection_type and slab.3. Specify the alias name without any mistake.4. Compare your executed result with the sample output given below.
Write a query to display the average 8th hour unit consumption from electricity reading. Give an alias name to the average 8th hour consumption as 'average_8th_hour_consumption
Write a query to display the sum of 12th hour unit consumption from electricity_reading table. Give an alias name to the sum of 12th hour consumption as 'sum_12th_hour_consumption'.Note:1. Display only the sum of 12th hour unit consumption from electricity reading.(12th hour reading will be h12 column).2. Specify the alias name without any mistake.3. Compare your executed result with the sample output given below.Sample output:
Write a query to display the average total_units of electricity consumption from the electricity_reading table.Give an alias name to the average units as 'average_units_of_electricity'.Note:1. Display only the average total_units of electricity consumption from electricity reading.2. Specify the alias name without any mistake.3. Compare your executed result with the sample output given below.Sample output:
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'.
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.