Q

How to write an SQL query using GROUP BY for row analysis

SQL expert Rudy Limeback explains how to write a SQL query using GROUP BY to retrieve the students who have all three rows in a skills table.

I have a table like this (two columns)

std_id  skill
   1    Java
   1    J2EE
   1    Struts
   2    Java
   2    J2EE
   3    Java
   3    J2EE
   3    Struts

Now I want to retrieve student IDs (std_id's) for those students that have three skills: Java, J2EE, Struts. Each student must posses all skills, which means my answer should be as follows:


std_id 
   1   
   3   

How do I write this query?

The students you're looking for are those that have all three rows in the skills table. This requires analysis of a group of rows, namely, the group of skill rows for each student. I would suggest to you that GROUP BY will be involved in the solution.

SELECT std_id 
  FROM skills
 WHERE skill IN ('Java','J2EE','Struts')
GROUP
    BY std_id
HAVING COUNT(*) = 3

The WHERE clause ensures that only the three rows of skills which we're interested in are retrieved from the table for each student. The GROUP BY and HAVING clauses ensure that all three were found.

How would you modify this solution to return, say, all students who had at least two of those three skills?

This was first published in September 2008

Dig deeper on Oracle development languages

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close