SQL Group By - Select Both Columns

Question

I have a table of users containing the following columns:

| User_ID  (int) | Name (varchar) |   Age (int)  |  Experience_Level (int) |

I would like to create an sql query to output all of the IDs of people who are not unique in the combination of age and experience.

My code so far:

SELECT Count(*), User_ID FROM Users 
GROUP BY Age,Experience_Level
HAVING Count(*) > 1

Obviously this is incomplete, This will group the non-unique users but it will not tell me all of their User_IDs.

Thanks in advance for your help!

score 3 · Accepted Answer · answered Jul 02 '10 at 01:00

3

Here's a negated logic query:

SELECT *
FROM Users
WHERE UserID not in
(
SELECT MIN(UserID)
FROM Users
GROUP BY Age, Experience_Level
HAVING COUNT(*) = 1
)

answered Jul 02 '10 at 01:00

Amy B

108,202
21
135
185

score 2 · Answer 2 · edited May 23 '17 at 12:18

2

Since you want information about groups of multiple users, how do you want this data returned? In a string containing comma-separated list of user_id values?

You didn't tag your question with the brand of SQL database you use.

If you use MySQL or SQLite, you can use the built-in GROUP_CONCAT() function:

SELECT Count(*), GROUP_CONCAT(User_ID) AS User_List FROM Users 
GROUP BY Age,Experience_Level
HAVING Count(*) > 1

By default, GROUP_CONCAT() separates the values with commas. Refer to the manual if you want it formatted another way.

There are other solutions for other SQL vendors. This question has come up many times on Stack Overflow:

edited May 23 '17 at 12:18

Community

1
1

answered Jul 02 '10 at 00:55

Bill Karwin

538,548
86
673
828

Cuz of the omission of `user_id` from the GROUP BY, only MySQL & SQLite support hidden columns in the group by (assuming the OP doesn't get an error when running the query). – OMG Ponies Jul 02 '10 at 00:58
@OMG Ponies: Assuming `GROUP_CONCAT()` is an aggregate (and it wouldn't make much sense otherwise), there's no reason it wouldn't work on other RDBMS's (assuming it, or something similar, exists). – Adam Robinson Jul 02 '10 at 01:03
Sorry I should have specified, I am using MySql. I've never heard of Group_Concat but the query works. I ended up using David B's answer for separation purposes but I learned something from this too! Thanks! – pws5068 Jul 02 '10 at 02:13
@Adam Robinson: My comment to Bill was about the OPs query to determine the database vendor. GROUP_CONCAT is only on MySQL & SQLite - search for comma separated list generation on SO. – OMG Ponies Jul 02 '10 at 04:13

score 1 · Answer 3 · answered Jul 02 '10 at 00:57

SELECT t.User_ID, t.Age, t.Experience_Level
FROM Users t INNER JOIN 
    (SELECT Age, Experience_Level
    FROM Users
    GROUP BY Age, Experience_Level
    HAVING Count(*) > 1) d ON t.Age = d.Age AND t.Experience_Level = d.Experience_Level

Test script:

create table Users (
User_ID int,
Name varchar(50),
Age int,
Experience_Level int
)

insert into Users (User_ID, Name, Age, Experience_Level) values (1, 'A', 33, 1)
insert into Users (User_ID, Name, Age, Experience_Level) values (2, 'B', 37, 1)
insert into Users (User_ID, Name, Age, Experience_Level) values (3, 'C', 33, 1)
insert into Users (User_ID, Name, Age, Experience_Level) values (4, 'D', 35, 2)
insert into Users (User_ID, Name, Age, Experience_Level) values (5, 'E', 33, 1)
insert into Users (User_ID, Name, Age, Experience_Level) values (6, 'F', 35, 2)
insert into Users (User_ID, Name, Age, Experience_Level) values (7, 'G', 18, 1)

score 1 · Answer 4 · answered Jul 02 '10 at 01:00

In theory, what you want is something like this, but unfortunately SQL Server doesn't allow it:

SELECT * FROM Users 
WHERE (Age, Experience_Level) IN
(
    SELECT Age, Experience_Level
    FROM Users  
    GROUP BY Age,Experience_Level 
    HAVING Count(*) > 1 
)

So instead, you have to settle for joining to a subquery:

SELECT Users.* FROM Users 
INNER JOIN 
(
    SELECT Age, Experience_Level
    FROM Users  
    GROUP BY Age,Experience_Level 
    HAVING Count(*) > 1 
) subq 
    ON Users.Age = subq.Age
    AND Users.Experience_Level = subq.Experience_Level

SQL Group By - Select Both Columns

4 Answers4