eliminating duplicates

Question

I want to pull users that only have ONE deviceID in a given month. Let's say I have the following table:

User    Device ID
Greg    1
Greg    2
Mike    3
Laura   4
Laura   5
Lisa    6
George  7
Peter   8
Paul    9
Paul    10
Jane    11

I only want to see Users who have ONLY one deviceID. so in this case Mike, Lisa, George, Peter and Jane. Is there a way to do this in SQL?

Answer 1

Expanding on Erik's comment.

Reference:

• SELECT - GROUP BY- Transact-SQL
• SELECT - HAVING (Transact-SQL)

DROP TABLE IF EXISTS dbo.device
GO
CREATE TABLE dbo.device
 (
  userName nvarchar(128), 
  device_ID int
  );
GO
INSERT INTO dbo.device
  (userName, device_ID)
VALUES
  ('Greg', 1),
  ('Greg', 2),
  ('Mike', 3),
  ('Laura', 4),
  ('Laura', 5),
  ('Lisa', 6),
  ('George', 7),
  ('Peter', 8),
  ('Paul', 9),
  ('Paul', 10),
  ('Jane', 11);
GO

--uncomment the line with COUNT if you want to see the count of device
SELECT 
  userName
  --COUNT(device_id) AS numberOfDevice
FROM dbo.device
GROUP BY userName
HAVING COUNT(device_id)=1;