I’m trying to do a wildcard retrieve if a defined variable is blank. But I can’t figure out the correct syntax. Answer It is as simple as this: The expression NULLIF(x, ”) will convert ” to null. Then we take advantage of how null comparison works: If @customerID is not null then first…
Tag: sql
Pivot and Sum in Amazon Redshift
I have a following tables table1 table2 first I’d like to join table2 as follows and then join table2 with id and pivot and sum up them I guess it is very complicated, are there any way to achieve this? In Redshift it seems that there is no way to pivot them. Thanks. Answer This looks to be a pivot
Select data where sum for last 7 from max-date is greater than x
I have a data set as such: Like this: Here’s the query i tried, This is clearly not right. What is a simple way to do this? Answer Find the max date by type. Then used it to find last 7 days and sum() the value.
subquery double where + aggreate functions
I have table and rows I want to have sum of balance and payed for simple users with approvedAdminId (isSimpleUser = 1 AND approvedAdminId is not NULL) and for not simple users (isSimpleUser = 0) Expected Result Answer Output:
Postgres function to return number of rows deleted per schema
I’m trying to adapt a Postgres stored procedure into a function in order to provide some feedback to the caller. The procedure conditionally deletes rows in specific schemas, and I’d want the function to do the same, but also return the amount of rows that were deleted for each schema. The origina…
Ensuring no dupe ids in query return
So for the following schema: You can see that there are 3 activities and each contact has two. What i am searching for is the number of activities per account in the previous two months. So I have the following query This returns: However this is incorrect. There are only 3 activities, its just that each cont…
How to search an sql lite database with python and print out the result in my ide?
I am trying to develop a method whereby it will allow the user to search by a chosen field and value eg. SELECT * FROM table WHERE column_field = ‘value’. It executes just fine in SQL lite but when I bring it into my IDE nothing is printing out. Any advice much appreciated. Answer From sqlite doc …
Omit rows where multiple columns contain NULL
I’m trying to figure out how to build a query that omits rows where both columns of interest are NULL. If only one or the other is NULL, then I would like to include that data. In this example table, I would like to have a query that includes everything except row 3. My thought would be to build a
SQL Can I use CASE statement with addition condition in a JOIN condition
I want to use join with case statement and addition condition, for example : But this code results in a syntax error. Can anyone help to explain a little? Answer Using Case When then in Join Condition and in Where Clause is not possible in this case What you are trying to achieve is doable with And and OR lik…
JSON Blob into SQL Server
I have data from a web API that I want to store in a SQL database. Each record in the web API looks like this: Answer That query is invalid. Should be of the form INSERT INTO TableName(Column1, Column2) VALUES (?,?), so something like: