The output keeps being 95.000000 I tried using AVG( CAST(Grades AS Decimal(10,2) ) ) inside the pivot, and it keeps returning a syntax error about (. Tried using CAST AS DECIMAL inside the FROM subquery, but it just keeps putting the same 00000 output. CAST AS FLOAT works but I need two decimal places and it …
Tag: string
Why do I get a conversion failed error when I use my attribute on my WHERE clause but it works when I don’t?
Why is my query returning a result when I run the following query: SELECT MAX(CAST(IssueDate as Date)) FROM (SELECT IssueDate FROM [Transient].[Commissions_TIB] WHERE ISDATE(issuedate) = 1 …
Why do double quotes influence result in compareing datetime?
Why do these two results dont return the same results? Can anyone explain why？ (addtime is datetime type.) Answer This does not do what you want: Unquoted 2020-10-10 is an arithmetic operation, which returns integer value 2000. You are then asking MySQL to compare it with a date, so it tries to convert the in…
Create a PostgreSQL view from a unique list of exploded data
When I make this request: I get results like this: Each line has values separated with / (space, slash, space). How to create a view with a list of unique values with count ? Answer You can split the string to rows with regexp_split_to_table() in a lateral join, then aggregate: Demo on DB Fiddle: val | cnt :&…
Execute different WHERE statement based on IF conditions depending on @variable?
I have a nativeQuery in my DAO layer inside my Spring Boot app. Basically, I’ passing three parameters, like so: I would like to have different WHERE statements executing depending on value of companyName, something like this: Or Answer Use boolean logic:
Mysql comma seperated to json-array
I have some fields in the database that are comma selerated, something like: a,b,c,d,e and I want to convert them into: I know how to do it in nodejs / any other language, but I need to do it directly on the mysql Possible? Thanks Answer The simplest approach probably is to use string functions only: Basicall…
i need a query to update column transferred_to to ‘Invalid_destination’ in Storage table if transferred_to column is equal 100
the query that i tried is this But i got this output so this is my table Answer You seem to want: Note that storage is a language keyword in MySQL, hence a poor choice for a column name. It would be simpler to use another column name, otherwise you need to quote it.
Select from many to many relations table
Given these tables: I would like to query scenarios with all the connected suites. results: **scenario** **suites** loginScenario loginSignup, endToEnd, smoke addToCart …
Selecting single row if it matches another
I have a scenario of this type: I want to select only one row in such a scenario. How can I achieve this? Answer You could use distinct on with least() and greatest(): An alternative is not exists: Finally: if all of the rows are duplicated, then a simple where condition is sufficient:
Count rows with equal values in a window function
I have a time series in a SQLite Database and want to analyze it. The important part of the time series consists of a column with different but not unique string values. I want to do something like this: I don’t know how to get the countValue column. It should count all Values of the partition equal to …