These TSQL tables are exact but I can’t guarantee that. I cannot guarantee which rows will be non-existent in either table. What I want to be able to do is to subtract one from the other into a new table. For instance, “Agg Asslt” should have a column value for “OffenseCount” of …
Tag: sql
Why two logically same conditions in postgresql case clause have different behavior?
I have two queries is postgresql: As you might have noticed result of the first condition in both queries is true, but result of first query is null and result of the second one is ERROR: division by zero What is happening in here? Is there any optimization in order of evaluation occurring? If yes, is there a…
is there a sql query I want to get last job_id with the earliest start date
is there a sql query I want to get last job_id with the earliest start date. I have a table named employee expected results Answer First, use FIRST_VALUE() window function to get the latest job_id for each employee_id and then use conditional aggregation to get the earliest start_date for this job_id: See the…
How to execute custom logic at pyspark window partition
I have a dataframe in the format shown below, where we will have multiple entries of DEPNAME as shown below, my requirement is to set the result = Y at the DEPNAME level if either flag_1 or flag_2= Y, if both the flag i.e. flag_1 and flag_2 = N the result will be set as N as shown for DEPNAME=personnel
Getting empty result from a query
I have Client table ClientGuid Email Phone Also have Activity table ActivityTitle Date ClientGuid If client does some “Activity” it is saved in Activity table. I want to get Emails of clients who did not have any activity in last three months and had activities before. Tried this : Getting empty r…
Postgresql – Looping through array_agg
I have a table from which I need to calculate the number of times intent_level changes for each id. Sample Table format : I couldn’t figure out how to loop through array_agg and compare consecutive elements. Below I tried using array_agg, but then I don’t know how to loop through it and compare co…
get most frequent values in every month in 2021
Trying to get the most frequent values in every month from tables inspection table : can be ignored – > FOREIGN key (lno) REFERENCES restaurant) data : query : output: month id 3 333 4 222 5 222 6 333 expected output – month id 3 333 4 222 5 111 6 222 6 333 Answer IMHO you don’t need
SQL Browser Crashing when running a Nested Query
I have a database with three tables: grocery nutrient grocery_nutrient (for many to many) Actually, the database is very big and I am facing the problems by running this query, the SQL browser crashes when I run, if I add LIMIT it works. and this is the query I have written for it. Answer You can simplify you…
Categorizing the columns of one row in SQL
I have a database table with records like the picture: So each record contains columns related to points of the project lifecycle. I need to categorize those columns in order to know how many columns are ‘completed’ or ‘In Progress’ or ‘Not started’ I want the output to be …
select count with another select and inner join
Is it possible to use two “select” in the same query? I tried it but got the syntax error several times. My query example: I would like the result shown to be all queries on the screen. Basically, what I want is that the result shown when executing the query is the first and second “select&#…