Give the table “shop” Is there a way to find the list of products(whether A or B) whose price is less or equal to the average price or type A? I tried the following: Answer Use a subquery to find the average price of type A items: Just for fun, we could also use analytic functions here: Demo
Append Group with data from another table based on its existence in first table
I have a table like this I have another table with these data Based on the second table I need to find which phases are missing for each Project codes So I need to get a result like this I tried cross join it doesn work since its creating 4 entries for each one item in the first table. So
Unable to unnest in bigquery when there is comma inside list which is of type string
Unable to unnest in bigquery when there is comma inside list which is of type string. I have data in following format where value is type of string When I use following query, It is incorrectly splitting and unable to get logic right in regexp_replace With the above code, I am getting My expected output is I …
Create new Event_ID based on ID with sliding window on date column
Imagine I have a table like ID Date 1 2021-01-01 1 2021-01-05 1 2021-01-17 1 2021-02-01 1 2021-02-18 1 2021-02-28 1 2021-03-30 2 2021-01-01 2 2021-01-14 2 2021-02-15 I want to select all data on this table, but creating a new column with a new Event_ID. An Event is defined as all the rows with the same ID, wi…
Mysql if Null or Empty String then Show N/A
Hello I’m new to mysqsl and I’m trying to display N/A if the return data is empty or NULL but my query is unrecognized statement type. What do you think is wrong with my query? wrong placement of code? END Answer Use IF with ISNULL here: If you really wanted to use a CASE expression, then it would…
How to distribute different types of values from one column into individual columns
I have one table that stores different values in one column. Lets say the structure is like: column_a: name column_b: contact_type – email or phone column_c: contact_value – exact email address or phone number Shall a person have both email and phone, there are two rows. I trying to get a select t…
Oracle move strings to another matching row
I have a table called tblrespondent and my data looks like this: I have several case numbers that have multiple rows like this and I need to move all the comments into the lowest orclid13. The result would look like this with a semicolon in between each comment. How would I approach this? Many thanks in advan…
Json extract coming as null – Presto SQL
I`m trying to use json extract to get some values using the code below: And it works, however the values are coming all as NULL . When I extract the properties field I get the values that I want, as the example below: {“allow_url”:false,”partner_target_app_id”:[28479748204829001,938801…
How to re-use result from a SELECT statement?
My goal is to re-use the result from a SELECT statement to be used in SQL EXISTS statement. The general idea looks like this: The actual SQL statement I am trying to reduce: Answer This query: Doesn’t really make sense. It is saying to return all rows if col = 1 is in the table — but then it filte…
Sort full join based on two columns on two different tables
I have two tables in my PostgreSQL database (Table A and Table B). Both of these tables have a createdAt column. I want to do a full join on these two tables and then sort the result based on createdAt values on both A and B tables. Below is an example of what I want to be my query result.