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 …
Order table by the total count but do not lose the order by names
I have a table, consisting of 3 columns (Person, Year and Count), so for each person, there are several rows with different years and counts and the final row with total count. I want to keep the …
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:
Why can’t I add a column to an existing table with a checkConstraint that references other columns in SQL
I’m using SQL Server and am trying to add a column and a check constraint. I’ve found that the following works: However a less verbose way of writing this does not work: The following error is output: Column CHECK constraint for column ‘isTrue’ references another column, table ‘t…