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. …
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 …
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 en…
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 t…
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 cat…
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