Tag: sql

MySQL Convert alphabet to number (e.g ‘A’ to 1, ‘B’ to 2…)

I’m making mysql SP, which has an input of type VARCHAR(20). What I want to do is to convert the last character of input string to a number. However last character can be number or letter, so if it is number, converting is not needed. For example, input ‘APPLE’ will be converted to ‘5&…

How to replicate nested from statement to nested with statement?

nested sql with-statement

I have created a nested from statement that can be seen below: I am trying to recreate the same results with a nested with statement, I have attempted the below, but it results in a multi-part identifier could not be bound. How would I solve this issue because I thought I named each query correctly? Answer Th…

Calculating percentages in SQL query

sql sql-server tsql

I have a table in SQL that has two columns of data, first is a week number, second is a value. There can be multiple records for the same week. Here’s some sample data (I added some SQL for this data at the end): id WeekNo MyAmount 1 1 5200.00 2 1 180.00 3 1 100.00 4 2 100.00 5

Showing null instead of throwing a zero division error by try-catch

sql sql-server stored-procedures try-catch

Why does this procedure return NULL when count and sum are both 0? and does not produce output like this? How to change the first procedure to act same as the second one when there is nothing in the table, meaning when sum and count are both 0? Thanks. Answer If there is nothing in the table then you have

How to do SQL insert for Postgres using Prisma with condition on count of rows?

prisma prisma2 sql

I am trying to insert a row into database provided that – number of rows satisfying some condition already in the table is less than certain threshold, say 10. For example, in below model, I don’t want to have a project to have more than 10 users; so count(projectId) should be less than 10: In gen…

Why does this query return all the rows?

sql sql-server tsql

I have the following code: I want to get all the students who have a min grade 6, and I need to do it in subquery in where (most wanted to do it with exists, but not obligatory). My query right now returns all the students, however subquery gets not all of them. Answer If just one grade satisfies the

Error while converting timestamp string with timezone (+0000) to Timestamp in Presto

presto sql timestamp-with-timezone timezone timezone-offset

I am trying to convert a timestamp string to timestamp with date_parse, but keep getting an error. Any suggestions? I am working on Presto SQL. I also refered: http://teradata.github.io/presto/docs/127t/functions/datetime.html, but couldnt find anything that can deal with +0000 i.e Timezone. I tried: throws m…

How to convert nvarchar string mm/dd/yyyy hh:mm:ss to datetime?

datetime sql sql-server tsql

I have a nvarchar column with dates formatted like this: “04/18/2021 13:04:15” (without quotes). I want to change this column to be datetime and migrate all of it data. I’ve looked for format codes and figured out, that closest code to my variant is U.S (101). So I tried to But I get error: …

SQL Trigger with loop from another table

database-cursor for-loop sql triggers while-loop

I’ve got 2 tables : Table A Table B The table A contains the following values : The Table B contains the following values : My goal is to set the column B.chk with the number of occurence from table A when a new row is created on table B. For example : The trigger on table B should set

Store Pricing Combinations and Sums w/ SQL

combinations permutation sql sum unique

I have a table that looks something like and I would like to create all possible combinations with the sum of the prices, including the unique values to create something like what would be the quick and dirtiest way of accomplishing this? Answer Use three joins: