Group & Sort Aggregated Meter Usage Data by Site ID & Service Type
General Explanation
What is this recipe?
This recipe shows how to aggregate monthly, quarterly, or annual meter usage data by site ID, allowing for insights into usage patterns across different sites over time.
Use case
What is this recipe for?
This recipe is for users that want to see aggregated usage values for all meters for a given site and service type. This can be useful for analyzing usages at different facilities when the meter-level view is too granular. Grouping by location can also help in calculating emissions if emission coefficients can be correlated to these locations.
Snowflake SQL
What’s the Snowflake SQL for this recipe?
What does the SQL for this recipe mean?
- This first query shows how to aggregate the monthly usage values by site_id, service_type, and calendarized_month. This sums the monthly meter usage values so that you can see the total usage by site and service type.
select site_id
, calendarized_month
, round(sum(standardized_monthly_measured_usage), 2) as monthly_measured_usage
, any_value(standardized_monthly_measured_usage_unit_of_measure) as unit_of_measure
, service_type
from <YOUR_SNOWFLAKE_SHARE>.shares.monthly_usages
where
standardized_monthly_measured_usage is not null
and site_id is not null
group by site_id, calendarized_month, service_type
order by site_id, calendarized_month, service_type;
This query orders by site, month, and service type. You can change the order by
statement to surface other types of information:
- To sort by the highest usage by site by service type regardless of month:
order by site_id, service_type, monthly_measured_usage desc
- To sort by just the highest usage by service type:
order by service_type, monthly_measured_usage desc
- The next query shows how to aggregate quarterly usages.
select site_id
, calendarized_quarter
, round(sum(standardized_quarterly_measured_usage), 2) as quarterly_measured_usage
, any_value(standardized_quarterly_measured_usage_unit_of_measure) as unit_of_measure
, service_type
from <YOUR_SNOWFLAKE_SHARE>.shares.quarterly_usages
where
standardized_quarterly_measured_usage is not null
and site_id is not null
group by site_id, calendarized_quarter, service_type
order by site_id, calendarized_quarter, service_type;
The additional sort by
expressions documented with the first query can also be used here.
- This last query shows how to aggregate annual usages.
select site_id
, calendarized_annual
, round(sum(standardized_annual_measured_usage), 2) as annual_measured_usage
, any_value(standardized_annual_measured_usage_unit_of_measure) as unit_of_measure
, service_type
from <YOUR_SNOWFLAKE_SHARE>.shares.annual_usages
where
standardized_annual_measured_usage is not null
and site_id is not null
group by site_id, calendarized_annual, service_type
order by site_id, calendarized_annual, service_type;
The additional sort by
expressions documented with the first query can also be used here.
Return format
What’s the return format and an explanation of each returned field?
Monthly usage by site and service type
SITE_ID | CALENDARIZED_MONTH | MONTHLY_MEASURED_USAGE | UNIT_OF_MEASURE | SERVICE_TYPE |
---|---|---|---|---|
sit_1EC… | 2023-01 | 2342.32 | gallons | water |
sit_38D | 2023-01 | 2838.00 | kwh | electric |
Quarterly usage by site and service type
SITE_ID | CALENDARIZED_QUARTER | QUARTERLY_MEASURED_USAGE | UNIT_OF_MEASURE | SERVICE_TYPE |
---|---|---|---|---|
sit_1EC… | 2023-q1 | 3828.00 | gallons | water |
sit_38D | 2023-q1 | 5521.10 | kwh | electric |
Annual usage by site and service type
SITE_ID | CALENDARIZED_ANNUAL | ANNUAL_MEASURED_USAGE | UNIT_OF_MEASURE | SERVICE_TYPE |
---|---|---|---|---|
sit_1EC… | 2023 | 234283.00 | gallons | water |
sit_38D | 2023 | 283802.23 | kwh | electric |
Updated 6 months ago