I am trying to get how many orders each supplier have, by showing two columns, CompanyName and number of orders. And then Sorting them by descending order. My SQL: But I get the following errors: Msg 145, Level 15, State 1, Line 65 ORDER BY items must appear in the select list if SELECT DISTINCT is specified. Msg 8120, Level
Tag: sql-server
SQL Selecting & Counting From Another Table
I have this query that works excellently and gives me the results I want, however, does anybody know how I can remove any rows that have 0 orders? I am sure it is something simple, I just can’t get my head around it. In other words, should it only show the top 2 rows? ID Company Org Total Orders 1232
Converting a half-hourly data into Quarter-hourly data by taking mean of half-hourly values in SQL?
I have half-hourly (30 min interval) Data Table in SQL (SSMS). I want to convert it to Quarter-hourly (15 min interval) by taking mean/average of the preceding and succeeding values. What SQL query should I run for it? Answer Using cross apply to convert a single row to a pair of rows
Difference between dm_exec_sql_text vs dm_exec_input_buffer
As per Microsoft docs, dm_exec_sql_text returns the text of the SQL batch that is identified by the specified sql_handle while dm_exec_input_buffer returns information about statements submitted to an instance of SQL Server. I used DBCC INPUTBUFFER for getting the last executed SQL. But it seems both dm_exec_sql_text and dm_exec_input_buffer are replacement DBCC INPUTBUFFER. If this is the case then what
Trying to write a query to specify if a customer has completed two types of a specific column
I have this table (resedtl) below… It contains information about a customer and the resType and the status of it. I am trying to determine if a customer has completed (status = done) both types of “RES” or only one or only two. I want to return something like this, where if custId is done for both resType 1 and
Sql server Balance Table Update Recursive
I Have a SQL Server database with a table named Balance The table: Id Date IN OUT Balance 3345312 2022-08-07 100 50 250 5435245 2022-08-06 50 50 200 4353451 2022-08-05 0 100 200 5762454 2022-08-04 20 100 300 7634523 2022-08-03 400 100 380 5623456 2022-08-02 100 20 80 4524354 2022-08-01 0 0 0 Id = Unique Identifier Date = Balance
Number of Customer Purchases in Their First Month
I have a list of customer orders. I can easily calculate the month and year of first purchase for each customer (e.g. customer 1 had their first purchase in Sept 2021, customer 2 had their first purchase in Oct 2021, etc.). What I want to add is an additional column that counts the number of purchases a customer made in
Trying to find the max observation by group
I have the below code where I am trying to find a patients injury level and whether it is between 1 and 5 or 6 and 10. I have used the ‘case when’ clause below. The issue is, some patients have multiple injury levels. Is there a way for me to only get the max injury level and ignore the
Select highest aggregated group
I’m having trouble with selecting the highest aggregated group. I have data in a table like this: Sales table: ID GroupDescription Sales 1 Group1 2 1 Group1 15 1 Group2 3 1 Group3 2 1 Group3 2 1 Group3 2 2 Group1 2 2 Group2 5 2 Group3 3 2 Group4 12 2 Group4 2 2 Group4 2 I want
Remove overlapping date intervals
I have two tables in my sql server database : PlannedPeriods : containing periods of future events UnavailabledPeriods : list or periods that are not available Here’s an example for structure and data : I would like to remove all unavailable periods from the planned periods in order to obtain this result : Answer In this case you should be