Let’s take the following data: It can also be generated in BQ with the following statement: How would I do the following two ‘distinct’ totals on the right of the following: That is, I want to get a “total” that doesn’t double-count, or rather, only gets the distinct items …
How to make a SQL query partial match with substrings not in the beginning
Let’s say there is a column called: m_report_notation Said column, has values such as: NM_004004.5:c.235del (p.L79Cfs*3) And I would like that, if the value of v is, for example: 235del, then get NM_004004.5:c.235del (p.L79Cfs*3) as a result. This is my current query and what I’ve tried so far (us…
Updating a specified row on a table with information from another table using a trigger and function on PostgreSQL
I have a database for a petshop for class that contains the table Animals with IDs and the dates of the last consult requisition for each animal, amongst other irrelevant columns and the table Requisition which contains the animals’ IDs and requisition dates. And I need to create a trigger that will upd…
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?
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
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
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?
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?
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
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…