I have this query which shows the below result, I want to use this MarksObtained and take out min, max and avg of each course and show it with the second query (have provided below). This is the second query So it will basically show min, max and avg of each course achieved by the students. What I want: Sampl…
Tag: tsql
Sum values in two different tables and join results keeping the columns
I have two tables: one with downtime and the other with productive time. I want to have a table like this But I am getting this In the result, I am getting twice the downtime of the sum for the report 04102021-1, but as can be seen in the second picture, the value is present only once. The script I
Suggested way to do a ‘parallel period’ SQL statement
Let’s say I want to get the profit between two dates. Then I can do something like this: I would then like to compare it to a previous period offset by a fixed amount. It could be written something like this to get it in two rows: Is there a way to do this without a union? I am looking
Decreasing Cumulative Sum That When Equal to or Less Than Zero Change Another Value
Below we have two tables, one of purchase orders and the other of sales orders. What I’m trying to do is assign each sales order to a purchase order, with free stock. Which I can do with the following query: Table 1 – Incoming Purchase Orders: number item shipDate qty usedQty freeQty 12 Toy 2021-1…
Find names based on max number dynamically in SQL
I have a data set like this: Table A: What’s the maximum number of Place on which a Partner has spent money and who are these Partners? Display only the Partners reaching this max number I tried this: But I feel like its not the right logic. What am I missing? Answer There’s really no need for you…
T-SQL Logic (Where exists one column but not the other)
I need a bit of logical help in writing a query. Here is the set up. I need a query that returns all records from main_table where the id of main_table matches the id of sub_table, but seq of main_table does not match seq of sub_table. Expected result is id seq A1 3 A2 2 A2 3 My attempt What
Divide the sum of grades using pivot operator
Output: Expected output: Answer Each student may have two marks in each subject? Make sure of that first of all Try to use the average AVG(Grades)
Does Adding Indexes speed up String Wildcard % searches?
We are conducting a wildcard search on a database table with column string. Does creating a non-clustered index on columns help with wildcard searches? Will this improve performance? Proposed Index: for this query Currently using Microsoft SQL Server 2019. Answer Creating a normal index will not help(*), but …
Not getting data when trying to create multiple inner join on 2 columns from the same table
I have a table and data like below: Employee: LeaveRequest: LeaveUpdateLogs: Now here I want to calculate statistics as below for a particular department: Total number of requests sent and received for DepartmentId 100. But I am confused here for getting data for “Sent” and “Received” …
Modify query so as to add new row with sum of values in some column
I have table in SQL Server like below using below code: How can I modify my query in SQL Server so as to add new row with sum of values in col2? So I need something like below: Answer You could use ROLLUP for this. The documentation explains how this works. https://docs.microsoft.com/en-us/sql/t-sql/queries/s…