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…
Query dates from different rows in the same table to appear on the same row in the query
I have seen many posts about this issue using joins but not for the same table. I am trying to compare the dates from different rows on the same table. I am writing tax software that tracks liens. The tax charge is on one line and any interest charged is on another. I need to compare the dates of the
Finding rows in SQL where changes but only certain changes while keeping others
I have this scenario where I want each occurrence of an active row to bring back that row in my result set and also inactive if there is only 1 inactive record for that IDENTIFIER and also if there are more than 1 active also show those. I’ve used Row_Number function and then in another query show where…
sql selecting unique rows based on a specific column
I have an table like this : I am trying to filter out something like this based on Col1 I am trying to get the all unique rows based on the values in Col1. If I have duplicates in Col1, the first row should be taken. I tried SELECT Col1 FROM tblname GROUP BY Col1 and got unique Col1 but
SQL table.column not working when I use it with full join
I have this SQL query: And it returns the error : “unknown column main.cname in filed list”. while the column certainly exists: seems alright. I tried using aliases but it just made it worse. I’m clueless Answer As coyeb60297 has pointed out, MySQL doesn’t do full outer joins so your q…