I’m starting with SQL and doing some exercises and I’m completely stuck on the last one. It is about looking for streets in the same country name. Two tables are used, locations and countries from the HR schema. The problem is that I don’t know how to avoid duplicate results. For example if …
Access Bare Columns w/ Aggregate Function w/o adding to Group By
I have 2 tables in postgres. users auth0_id email 123-A a@a 123-B b@b 123-C c@c auth0_logs id date user_id client_name abc-1 021-10-16T00:18:41.381Z 123-A example_client abc-2 … 123-A example_client abc-3 … 123-B example_client abc-4 … 123-A example_client abc-5 … 123-B example_client …
SQL relative percentage for each country
I have a table: maps_query like below: The question is to output the relative percentage of queries for maps_query for each country. Desired output is like below: I don’t quite understand what relative percentage is here but I assumed it’s asking to output (a country’s search_query counts/ a…
Getting ‘error: syntax error at or near…’ in Postgresql insert query
I’m fairly new at Postgresql and learning new things everyday. So I have this blog project where I want to use PostgreSQL as a db. But I’m kind of stuck at the most basic insert query which is throwing an error. I have three tables, posts, authors and categories. I could create the table properly …
SQL duplicates despite grouping on all key variables?
I am new to SQL and use it for work, so I am going to censor the real names of columns going forward in the query below. I am writing a query where the necessary data is spread across 3 tables. I have a network with users who send and receive packages from different nodes, and I want to see
Pivoting but handling Column Names
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 aggrega…
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 b…
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 sho…
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 so…
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. Unfor…