If I have a table like the following which is the number of records with a given label grouped by a certain category (in this case Male, Female), How would I transform it into a table that keeps the top x (e.g. 5 records) for each category, when the rows are sorted by number descending, and sums the remaining…
Tag: sql-server
SQL Update Only when Max Date reached
I’ve two tables like: Table1 Table2 The scenario is: If I update the Table2 on TID2 = 2, the LastStatus on Table1 shouldn’t be updated because there’s a MAX Date on Table2 with TID1=1. So LastStatus on Table1 will only updated if there’s an update on Table2 with MAX Date. Currently, I …
SQL Mathematical operator between queries
I’m wondering what the best method would be of using two separate query results in a mathematical operation. select count(*) as french_films from language l join film f on l.language_id = f….
SQL Query to calculate the AVG of DATEDIFF
I’m trying to write a query that gives me a gives me the AVG of a Datediff I have a similar query that I am trying to replicate with my new and here it is: SELECT DATENAME(MONTH, d.OPENED) AS …
SQL Query to categorize results of separate query
I am trying to run a query on the results of a separate query to update the Route_Type column for each record based on unique Route_Code and Delivery_Day. For each distinct Route_Code from the select …
Case expression for null and not null columns in a table
My table: Query tried Basically I want that if there is any single null bvalue for any column in table StudentScore, then the type of that column should be null else it should be not null (note that this is part of interview question and I cannot use information_schema etc. I need to do this using case. Can a…
find missing records in corresponding tables
Let’s say I have two temp tables with data that looks like this… Here’s what I am trying to achieve 1) Exclude all records from #TEMP1 and #TEMP2 that have identical match on IdNO, CdId, ApptDate, PROC=Y I’m only interesting in finding these scenarios: 1) Find record from #Temp1 or #Te…
Joining one table twice and counting records
I have 2 tables. One is master and thee other is a lookup. TblMstr and Cmaster Tblmstr Cmaster I need to get how many Registered Nurse for each question have Strongly Agree, Disagree and Agree. Similarly for Nurse Practitioners and Pharmacists. Answer Hmmm . . . You can join twice. Once to get the occupation …
Number of Null and non-null values for each column in SQL for a given table
I am trying to find the number of null values, non-null values, total rows for each column in a table named StudentScore. Code to create table: Code to find number of null, non null, total rows for each column in the table: I am getting below errors: Result I need: Answer The reason for the error is that INFO…
Comma-separated values to rows with sum in SQL Server
Output: I want to convert the output to this format: i.e. comma-separated all the orgs and sum of it’s area value. Answer Sub-query is really not necessary :