0

Everyone,

I have a question - how to show the (possibly ambiguous) column that corresponds to the value retrieved by an aggregating function?

Related to the dataset below, the task is to find the DepartmentID with the lowest average Salary.

The below sub-query usage retrieves an error: "Column 'Initial.DepartmentID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

I have researched other topics here and it should lie on the ambiguity of the DepartmentID to show in case the lowest average salary is the same across multiple Departments.

SELECT DepartmentID, MIN(AverageSalaries) FROM (
            SELECT DepartmentID, AVG(Salary) AS AverageSalaries FROM Employees
            GROUP BY DepartmentID )
            AS [Initial]

By omitting the DepartmentID from the above query the ambguity is gone and the minimum average Salary is shown:

SELECT MIN(AverageSalaries) FROM (
            SELECT DepartmentID, AVG(Salary) AS AverageSalaries FROM Employees
            GROUP BY DepartmentID )
            AS [Initial]

Is there a way to get the DepartmentID to show next to it corresponding lowest average Salary, using subquerues instead of self-JOIN? How to proceed in such cases in general?

USE [TestDatabase]
GO
/****** Object:  Table [dbo].[Employees]    Script Date: 12.6.2020 г. 20:05:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Employees](
    [EmployeeID] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [varchar](50) NOT NULL,
    [LastName] [varchar](50) NOT NULL,
    [MiddleName] [varchar](50) NULL,
    [JobTitle] [varchar](50) NOT NULL,
    [DepartmentID] [int] NOT NULL,
    [ManagerID] [int] NULL,
    [HireDate] [smalldatetime] NOT NULL,
    [Salary] [money] NOT NULL,
    [AddressID] [int] NULL,
 CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED 
(
    [EmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Employees] ON 
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (1, N'Guy', N'Gilbert', N'R', N'Production Technician', 7, 16, CAST(N'1998-07-31T00:00:00' AS SmallDateTime), 12500.0000, 166)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (2, N'Kevin', N'Brown', N'F', N'Marketing Assistant', 4, 6, CAST(N'1999-02-26T00:00:00' AS SmallDateTime), 13500.0000, 102)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (3, N'Roberto', N'Tamburello', NULL, N'Engineering Manager', 1, 12, CAST(N'1999-12-12T00:00:00' AS SmallDateTime), 43300.0000, 193)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (4, N'Rob', N'Walters', NULL, N'Senior Tool Designer', 2, 3, CAST(N'2000-01-05T00:00:00' AS SmallDateTime), 29800.0000, 155)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (5, N'Thierry', N'D''Hers', N'B', N'Tool Designer', 2, 263, CAST(N'2000-01-11T00:00:00' AS SmallDateTime), 25000.0000, 40)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (6, N'David', N'Bradley', N'M', N'Marketing Manager', 5, 109, CAST(N'2000-01-20T00:00:00' AS SmallDateTime), 37500.0000, 199)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (7, N'JoLynn', N'Dobney', N'M', N'Production Supervisor', 7, 21, CAST(N'2000-01-26T00:00:00' AS SmallDateTime), 25000.0000, 275)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (8, N'Ruth', N'Ellerbrock', N'Ann', N'Production Technician', 7, 185, CAST(N'2000-02-06T00:00:00' AS SmallDateTime), 13500.0000, 108)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (9, N'Gail', N'Erickson', N'A', N'Design Engineer', 1, 3, CAST(N'2000-02-06T00:00:00' AS SmallDateTime), 32700.0000, 22)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (10, N'Barry', N'Johnson', N'K', N'Production Technician', 7, 185, CAST(N'2000-02-07T00:00:00' AS SmallDateTime), 13500.0000, 285)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (11, N'Jossef', N'Goldberg', N'H', N'Design Engineer', 1, 3, CAST(N'2000-02-24T00:00:00' AS SmallDateTime), 32700.0000, 214)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (12, N'Terri', N'Duffy', N'Lee', N'Vice President of Engineering', 1, 109, CAST(N'2000-03-03T00:00:00' AS SmallDateTime), 63500.0000, 209)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (13, N'Sidney', N'Higa', N'M', N'Production Technician', 7, 185, CAST(N'2000-03-05T00:00:00' AS SmallDateTime), 13500.0000, 73)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (14, N'Taylor', N'Maxwell', N'R', N'Production Supervisor', 7, 21, CAST(N'2000-03-11T00:00:00' AS SmallDateTime), 25000.0000, 82)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (15, N'Jeffrey', N'Ford', N'L', N'Production Technician', 7, 185, CAST(N'2000-03-23T00:00:00' AS SmallDateTime), 13500.0000, 156)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (16, N'Jo', N'Brown', N'A', N'Production Supervisor', 7, 21, CAST(N'2000-03-30T00:00:00' AS SmallDateTime), 25000.0000, 70)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (17, N'Doris', N'Hartwig', N'M', N'Production Technician', 7, 185, CAST(N'2000-04-11T00:00:00' AS SmallDateTime), 13500.0000, 144)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (18, N'John', N'Campbell', N'T', N'Production Supervisor', 7, 21, CAST(N'2000-04-18T00:00:00' AS SmallDateTime), 25000.0000, 245)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (19, N'Diane', N'Glimp', N'R', N'Production Technician', 7, 185, CAST(N'2000-04-29T00:00:00' AS SmallDateTime), 13500.0000, 184)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (20, N'Steven', N'Selikoff', N'T', N'Production Technician', 7, 173, CAST(N'2001-01-02T00:00:00' AS SmallDateTime), 9500.0000, 104)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (21, N'Peter', N'Krebs', N'J', N'Production Control Manager', 8, 148, CAST(N'2001-01-02T00:00:00' AS SmallDateTime), 24500.0000, 11)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (22, N'Stuart', N'Munson', N'V', N'Production Technician', 7, 197, CAST(N'2001-01-03T00:00:00' AS SmallDateTime), 10000.0000, 36)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (23, N'Greg', N'Alderson', N'F', N'Production Technician', 7, 197, CAST(N'2001-01-03T00:00:00' AS SmallDateTime), 10000.0000, 18)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (24, N'David', N'Johnson', N'', N'Production Technician', 7, 184, CAST(N'2001-01-03T00:00:00' AS SmallDateTime), 9500.0000, 142)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (25, N'Zheng', N'Mu', N'W', N'Production Supervisor', 7, 21, CAST(N'2001-01-04T00:00:00' AS SmallDateTime), 25000.0000, 278)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (26, N'Ivo', N'Salmre', N'William', N'Production Technician', 7, 108, CAST(N'2001-01-05T00:00:00' AS SmallDateTime), 14000.0000, 165)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (27, N'Paul', N'Komosinski', N'B', N'Production Technician', 7, 87, CAST(N'2001-01-05T00:00:00' AS SmallDateTime), 15000.0000, 32)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (28, N'Ashvini', N'Sharma', N'R', N'Network Administrator', 11, 150, CAST(N'2001-01-05T00:00:00' AS SmallDateTime), 32500.0000, 133)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (29, N'Kendall', N'Keil', N'C', N'Production Technician', 7, 14, CAST(N'2001-01-06T00:00:00' AS SmallDateTime), 11000.0000, 257)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (30, N'Paula', N'Barreto de Mattos', N'M', N'Human Resources Manager', 9, 140, CAST(N'2001-01-07T00:00:00' AS SmallDateTime), 27100.0000, 2)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (31, N'Alejandro', N'McGuel', N'E', N'Production Technician', 7, 210, CAST(N'2001-01-07T00:00:00' AS SmallDateTime), 15000.0000, 274)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (32, N'Garrett', N'Young', N'R', N'Production Technician', 7, 184, CAST(N'2001-01-08T00:00:00' AS SmallDateTime), 9500.0000, 283)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (33, N'Jian Shuo', N'Wang', NULL, N'Production Technician', 7, 135, CAST(N'2001-01-08T00:00:00' AS SmallDateTime), 9500.0000, 160)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (34, N'Susan', N'Eaton', N'W', N'Stocker', 15, 85, CAST(N'2001-01-08T00:00:00' AS SmallDateTime), 9000.0000, 204)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (35, N'Vamsi', N'Kuppa', N'', N'Shipping and Receiving Clerk', 15, 85, CAST(N'2001-01-08T00:00:00' AS SmallDateTime), 9500.0000, 51)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (36, N'Alice', N'Ciccu', N'O', N'Production Technician', 7, 38, CAST(N'2001-01-08T00:00:00' AS SmallDateTime), 11000.0000, 284)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (37, N'Simon', N'Rapier', N'D', N'Production Technician', 7, 7, CAST(N'2001-01-09T00:00:00' AS SmallDateTime), 12500.0000, 64)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (38, N'Jinghao', N'Liu', N'K', N'Production Supervisor', 7, 21, CAST(N'2001-01-09T00:00:00' AS SmallDateTime), 25000.0000, 55)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (39, N'Michael', N'Hines', N'T', N'Production Technician', 7, 182, CAST(N'2001-01-10T00:00:00' AS SmallDateTime), 14000.0000, 168)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (40, N'Yvonne', N'McKay', N'S', N'Production Technician', 7, 159, CAST(N'2001-01-10T00:00:00' AS SmallDateTime), 10000.0000, 107)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (41, N'Peng', N'Wu', N'J', N'Quality Assurance Supervisor', 13, 200, CAST(N'2001-01-10T00:00:00' AS SmallDateTime), 21600.0000, 39)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (42, N'Jean', N'Trenary', N'E', N'Information Services Manager', 11, 109, CAST(N'2001-01-12T00:00:00' AS SmallDateTime), 50500.0000, 194)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (43, N'Russell', N'Hunter', NULL, N'Production Technician', 7, 74, CAST(N'2001-01-13T00:00:00' AS SmallDateTime), 11000.0000, 258)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (44, N'A. Scott', N'Wright', NULL, N'Master Scheduler', 8, 148, CAST(N'2001-01-13T00:00:00' AS SmallDateTime), 23600.0000, 172)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (45, N'Fred', N'Northup', N'T', N'Production Technician', 7, 210, CAST(N'2001-01-13T00:00:00' AS SmallDateTime), 15000.0000, 282)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (46, N'Sariya', N'Harnpadoungsataya', N'E', N'Marketing Specialist', 4, 6, CAST(N'2001-01-13T00:00:00' AS SmallDateTime), 14400.0000, 106)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (47, N'Willis', N'Johnson', N'T', N'Recruiter', 9, 30, CAST(N'2001-01-14T00:00:00' AS SmallDateTime), 18300.0000, 99)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (48, N'Jun', N'Cao', N'T', N'Production Technician', 7, 38, CAST(N'2001-01-15T00:00:00' AS SmallDateTime), 11000.0000, 197)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (49, N'Christian', N'Kleinerman', N'E', N'Maintenance Supervisor', 14, 218, CAST(N'2001-01-15T00:00:00' AS SmallDateTime), 20400.0000, 118)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (50, N'Susan', N'Metters', N'A', N'Production Technician', 7, 184, CAST(N'2001-01-15T00:00:00' AS SmallDateTime), 9500.0000, 224)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (51, N'Reuben', N'D''sa', N'H', N'Production Supervisor', 7, 21, CAST(N'2001-01-16T00:00:00' AS SmallDateTime), 25000.0000, 249)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (52, N'Kirk', N'Koenigsbauer', N'J', N'Production Technician', 7, 123, CAST(N'2001-01-16T00:00:00' AS SmallDateTime), 10000.0000, 250)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (53, N'David', N'Ortiz', N'J', N'Production Technician', 7, 18, CAST(N'2001-01-16T00:00:00' AS SmallDateTime), 12500.0000, 267)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (54, N'Tengiz', N'Kharatishvili', N'', N'Control Specialist', 12, 90, CAST(N'2001-01-17T00:00:00' AS SmallDateTime), 16800.0000, 129)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (55, N'Hanying', N'Feng', N'P', N'Production Technician', 7, 143, CAST(N'2001-01-17T00:00:00' AS SmallDateTime), 14000.0000, 182)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (56, N'Kevin', N'Liu', N'H', N'Production Technician', 7, 210, CAST(N'2001-01-18T00:00:00' AS SmallDateTime), 15000.0000, 259)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (57, N'Annik', N'Stahl', N'O', N'Production Technician', 7, 16, CAST(N'2001-01-18T00:00:00' AS SmallDateTime), 12500.0000, 262)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (58, N'Suroor', N'Fatima', N'R', N'Production Technician', 7, 38, CAST(N'2001-01-18T00:00:00' AS SmallDateTime), 11000.0000, 86)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (59, N'Deborah', N'Poe', N'E', N'Accounts Receivable Specialist', 10, 139, CAST(N'2001-01-19T00:00:00' AS SmallDateTime), 19000.0000, 103)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (60, N'Jim', N'Scardelis', N'H', N'Production Technician', 7, 74, CAST(N'2001-01-20T00:00:00' AS SmallDateTime), 11000.0000, 88)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (61, N'Carole', N'Poland', N'M', N'Production Technician', 7, 173, CAST(N'2001-01-20T00:00:00' AS SmallDateTime), 9500.0000, 72)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (62, N'George', N'Li', N'Z', N'Production Technician', 7, 184, CAST(N'2001-01-22T00:00:00' AS SmallDateTime), 9500.0000, 58)
GO
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [MiddleName], [JobTitle], [DepartmentID], [ManagerID], [HireDate], [Salary], [AddressID]) VALUES (63, N'Gary', N'Yukish', N'W', N'Production Technician', 7, 87, CAST(N'2001-01-23T00:00:00' AS SmallDateTime), 15000.0000, 80)
GO

1

You can use the over () clause, which allows you to return the aggregate value in each row of the results without using group by. You create another subquery using over (), and then compare the value of each row with the minimum value:

SELECT * FROM (
  SELECT DepartmentID, AverageSalaries, MIN(AverageSalaries) OVER () AS MinSalary
  FROM (SELECT DepartmentID, AVG(Salary) AS AverageSalaries
    FROM Employees
    GROUP BY DepartmentID) AS Averages
) AS RunningMinimums
WHERE AverageSalaries=MinSalary

This query will return:

DepartmentID  AverageSalaries  MinSalary
----------------------------------------
15            9250,0000        9250,0000
| improve this answer | |
  • This works like a charm, thank you! – query_question Jun 13 at 6:47
-1

Sorry did not understand the original question, this should return the desired result:

SELECT TOP (1) WITH TIES
    DepartmentID,
    AverageSalaries
FROM
    (
    SELECT
        DepartmentID,
        AVG(Salary) AS AverageSalaries
    FROM
        Employees
    GROUP BY
        DepartmentID
    ) AS [Initial]
ORDER BY
    AverageSalaries

DepartmentID AverageSalaries


15 9250.00

(1 row(s) affected)

| improve this answer | |
  • Could you please elaborate more, how should the whole query be? – query_question Jun 12 at 19:01
  • 1
    This code won't work because adding the group by would the return the minimum value by each department, not the id of the minimum value (and since the subquery performs another aggregate function it will return the same number of rows as there is only one value per department). – Alberto Martinez Jun 12 at 22:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.