Table columns : My table column details has json object like I want to write a query which will give me records like below : I tried with RESULT WITH COUNT : EXPECTED Answer To get all the objects from json array along with other columns you can use OpenJson() and Cross Apply as below: Query: Output: id cnt n…
Get consecutive count on repeated values with complex partition by in Postgres
Given a set of 3 columns (blue columns: student, semester, grade) I need to calculate for how many consecutive semesters has each student had the same grade (green column) using a query in Postgres. (…
How to update table from another table, with 2 columns
I am using SQL Server database and want a way to update MachinesSummary.ShareCount. Here are my two tables MachinesSummary ID Machine1 Machine2 ShareCount ——————————- 1 A J …
Incorrect Syntax – With Statement
I have a query which truncates and inserts data from the stored procedure table. However, I am not able to create stored procedure since I have ;With statement after INSERT INTO table. The query is …
Spring Data – The column name note_id was not found in this ResultSet
I’m getting the error “The column name note_id was not found in this ResultSet” when trying to access Postgres in spring but don’t when testing the query directly in Postgres. I’ve looked at …
Update SQL database registers based on JSON
I have a table with 30k clients, with the ClientID as primary key. I’m getting data from API calls and inserting them into the table using python. I’d like to find a way to insert rows with new clients and, if the ClientID that comes with the API call already exists in the table, update the existi…
How to subtract the cumulative values of confirmed, recovered and deseased?
I would like to subtract the cumulative values of confirmed, recovered and deceased. However, it does not work, Google BigQuery keeps giving me this error message (look at the pic). All attributes are INTEGER. Answer The error message is pretty clear. You have mixed types in the data — suggesting that s…
SQL – Cast Column as XML then Query Value from said column
I have a column with a bunch of unformatted XML code. I am trying to really just query 1 value out of the column. The value inside of the column is listed below: The value that I am looking for is displayValue=”NSharePoint Read Item” which is located in the line: I have the following query: which …
SQL Find list of tuples that fulfil at least one of the conditions
Question: Find the restaurant A that fulfils at least one of the following conditions: Conditions Restaurant A that is situated in ‘Central’ area or Restaurant A that sells at least 10 pizzas or Price of every pizza sold by A is at most $20 Expected outcomes: List with (restaurant name) Database S…
Multiple dynamic FIND_IN_SET searches
So, I have an array like this in my programming language: And I have a MySQL database table like this: Unfortunately, I cannot change the table to be more relational (yet). I want to pull out how many times an item in my array is present in the database value column. Now, the obvious way to do this would be