I have electric meter usage data for 22,000 meters that goes back 10 years (2012 to present) and it’s stored as hour intervals. So, each individual meter will have (10 years * 365 days * 24 hours) 87,600 records – one for each hour going back 10 years and the KW (usage) for that hour is recorded. And for each
Tag: group-by
SQL Server => How to use “case when” on multiple column to show a formula result based on a condition in the same line
I’m trying to provide an overall result of a formula ([CALCULATION]) and then in two extra columns ([NO_TENURE] & [TENURE]) the same calculation but using “CASE WHEN” to filter the information based on another column called [TENURE], everything in one single line like this: Right now is being shown this way: This is the code I’m using: I really appreciate
How to Use Queries to show records based on conditions
I have an SQL query that selects data based on uploaded and not uploaded this is the query am using to achieve the data above I want to go a step further and group the data into two columns. I want to get the labnames grouped using the uploadstatus that is 0 as Not Uploaded and the ones having 1
Simplifying SQL query
I’m using the Bixi public dataset found at https://www.bixi.com/en/open-data and have been asked to find “the average number of trips a day for each year-month combination in the dataset”. Here’s an example of the table from which I’m querying: id start_date start_station_code end_date end_station_code duration_sec is_member 85843 2016-04-15 00:00:00 6315 2016-04-15 00:05:00 6315 349 1 85844 2016-04-15 17:16:00 6315 2016-04-15
GROUP on one column and aggregate counts based on distinct values in another column
I have a table with values like so: I want to do a GROUP BY based on ts and display the counts for distinct values of usr_id like below. ts count of A count of B count of others 11 2 1 3 22 0 1 0 33 1 0 3 http://www.sqlfiddle.com/#!9/bbf4fc/2 I couldn’t make much progress beyond doing the
Count users with a condition in MYSQL
I have a table ‘helpdesk’ (detail below) that lists tickets. I’m trying to get the top 10 users who report issues, however there is a complication in the fact that users can report an issue for another user (think secretary for their boss as an example). The table structure is… Using the example above the count should return User 1
SQL: finding (multiple) best students of a professor by grade
I have to find the students with the best grade of each professor with this given table “x”: Prof Student Grade A 1 1.0 A 2 1.0 A 5 5.0 A 6 1.3 B 3 1.2 B 4 2.0 … … … The result should look like this: Prof Student Grade A 1 1.0 A 2 1.0 B 3 1.2
Can you help me to correct this query in sql
ERROR at line 1: ORA-00979: not a GROUP BY expression Answer You are getting this error for WEIGHT_IN_PERCENTAGE. If WEIGHT_IN_PERCENTAGE is same for every row in a group you can use (SUM(POINTS) * max(WEIGHT_IN_PERCENTAGE)) or you can SUM(POINTS*WEIGHT_IN_PERCENTAGE) multiply it with POINTS before sum: OR
PostgreSQL: Create array by grouping values of the same id
Given the following input data: id category 1 A 1 B 2 A 2 R 2 C 3 Z I aim aiming to get the following output table: id categories 1 {“A”,”B”} 2 {“A”,”R”,”C”} 3 {“Z”} using the following query: But what I get is the following table: id categories 1 {“A”,”B”,”R”,”C”,”Z”} 2 {“A”,”B”,”R”,”C”,”Z”} 3 {“A”,”B”,”R”,”C”,”Z”} How can I obtain
Alter SQL column based on comparison of number of elements
I have a table with columns (Id, Prod, Id) and I must create a (Flag) like this: This (Flag) is created by grouping by (Id2) and assigning 0 for the lowest (Id) and 1 otherwise. I tried using group by to no avail. How can I do that? Answer You can use a case expression with a windowed function: Example