I’m currently getting a result-set back as follows: What I’m trying to do is get the results to appear as follows: I’ve put together the following query, and I was curious as to whether someone had a better way of doing it: Here’s the sample data: Answer You can use conditional aggregation. That is, aggregate functions wrapped around CASE expressions.
Tag: sql
Get historical average and count of a value where a date could exist more than once
I have a table with multiple equal date entries and a value. I need a table that calculates the historical value and the count of entries per date. I want to use the data to create some charts in gnuplot/etc later. Raw data: Output should be: If it is not possible to calculate two different columns, I would be fine
Get the newest two line per product and get price and date
I have made a query that extract the two newest lines per product. Each row shows id, productnumber, pricechangedate, price. Id Prod number Date Price Rank Order 71582 0071807993 2021-10-15 18:06:22 220.79 1 60533 0071807993 2021-10-15 13:22:46 220.79 2 Is it possible to some how concatenate these rows to show: Prod number Newest Date Newest Price Second Newest Date Second
Is SQL floating point sum affected by the order-by clause?
Unlike the pure mathematical real numbers, or even the rational numbers, the floating-point number types are not commutative-associative. This means, as is commonly known in numerical coding, the order of a list of floating-point numbers affects the value of the floating-point sum. It can be a good idea to sort the list to put the smaller numbers first before adding
Procedure: PL/SQL: ORA-00904: “QUARTER”: invalid identifier
I’m new to Oracle PL/SQL and I’m attempting to create a procedure, called pop_date_dim, which populates an already-existing date dimension called date_dim. It does so by accepting both a date (DDMMYYYY) and a number (n) as parameters. The date dimension is then populated by using a FOR loop. Unfortunately, I keep getting two errors. Error(142,9): PL/SQL: SQL Statement ignore and
SQLite Sum over children in self-referencing table
I have a ecosystems table, which is self-referencing because each ecosystem can have sub-ecosystems. Each ecosystem can also have a score (which represents how healthy the ecosystem is). The columns are (with example data): The full_slug column represents the full path from top-level ecosystem down. It is redundant as it can be deduced from the slug and parent_slug columns, but
New to SQL and having issues with Sytnax (VB.NET)
I am fairly new to SQL and databases in general (in an intro class in college right now) and I have run into a little snag. I am building a paid time off tracker for people in my department (they are like children and can’t keep track of their own things with the tools we already have). I currently have
SQL find the 2 highest score for each country
I have two tables: maps_query and map_time like below: The question is to find the 2 highest score for each country. Desired output is like below: I was trying to use row_number() over but don’t know how to complete the query. Answer This can be achieved by rank() instead of row_number(). A good reference article: https://spin.atomicobject.com/2016/03/12/select-top-n-per-group-postgresql/
Hibernate – make id value be able to be autogenerated directly in datasource as well
I’m making a Spring boot application with Hibernate ORM framework. I have an @Entity class in my code: I successfully can persist a Thing to my datasource. And thing_id is generated as expected. But I cannot insert a record directly in database manager (e.g. DataGrip): I have a warning and appropriate error when trying to insert: So how can I
Insert columns into new table where one of the columns doesn’t allow null values
I am attempting to insert 50 rows at a time from one table into another, however I am unable to bypass the ‘Not Null’ column in the table I am attempting to INSERT INTO. Please note I am not able to alter that column so it accepts nulls. I have 2 tables (table 1 and table 2). I am inserting