I’m trying to get an item “manufacturer” with an array of contacts and each contact contains an array of translations using this query but I get this error this is the ER diagram dummy data example: I tried to change the structure of the query to solve it but I wasn’t able to fix it. S…
Tag: sql
How to surround a column with double quotes in Netezza
I want to surround the selected column entries with double quotes whenever there is a special character say ‘ | ‘ in the column value . In the above query in the true condition I want to surround email by double quotes whenever email contains | . For ex if email is a|bc@gmail.com , I want it to be…
Performance impact of view on aggregate function vs result set limiting
The problem Using PostgreSQL 13, I ran into a performance issue selecting the highest id from a view that joins two tables, depending on the select statement I execute. Here’s a sample setup: What I found out I’m executing two statements which result in completely different execution plans and run…
SQL query to count rows based on condition? Keep getting syntax error for my count logic
So I want my query to return how many books total as a column, but how many books are returned as a column. I’m using count also applying a condition, but I’m getting error saying: Incorrect syntax near ‘)’. Anyway to achieve this? Answer Here is the syntax error count(bookstatus = R) …
Split single JSON array column and update multiple integer columns with this data
I have a table with a JSON array column containing an integer of every hour of the day, e.g.: In the same table I have added 24 new integer columns named i0 till i23 so I can easier filter on each individual column without complicated JSON queries. With which SQL query can I update the 24 columns with the val…
Postgresql how to change row to column
lets say i have a table like this: and i want to create a table like this with query what query should i type to show a table like above? using colpivot or crosstab? Answer You can try to use UNION ALL Query 1: Results:
Thousands separator in sql with dot in bigquery
How to cast numeric type let say 30000 into string type with dot as thousand separator 30.000. I tried to use But the result is 30,000 not 30.000 Answer You could just replace the , with . after formatting:
SQL: Return top 1% of values using NTILE function
I have a database with two columns as shown below, ID and COUNT_OF_ACTIONS where I want to return the top 1% of values from the COUNT_OF_ACTIONS – below might not be the best example, but imagine COUNT_OF_ACTIONS going from 0 to 100, so the query should return: 99 and 100 since these counts are >= 99…
Add rows accumulated by current month
Is there a way to translate my hard coded SQL script into a dynamic? My problem with this code is that, it still adding the SEPT-DEC it should be zero since we are not yet covered this month My table Expected Result: Starts from JAN then Added the succeeding covered months. Answer You can UNPIVOT your data, u…
Azure data upload and SQL database update failure for filename tracking
I have a scenario, in which I upload the file to Azure and will get the uploaded filename. Now I would need to store the filename (received for Azure upload) to SQL database. In case, if database down means it will fail to capture the filename (received for Azure upload) to SQL database but the File upload to…