Ask The Oracle Expert: Questions & Answers

SQL query to combine rows

SQL query to combine rows

By  Rudy Limeback, SQL Consultant, r937.com

SearchOracle.com

Suppose there is a table as below:

SkillName SkillCode Beginner Proficient Experienced 
Java        001         2         0          0 
Java        001         0         3          0 
Java        001         0         0          1

The above table suggests that for the skill Java, there are 2 Beginners, 3 Proficient and 1 Experienced people. I need to write a query which will query the above table and retrieve the results in the form given below, combining the rows:

SkillName SkillCode Beginner Proficient Experienced 
Java        001         2         3          1

Can you explain me as to how to go about this? An example would be much better. Thanks in advance.

The best example to explain this concept is your own:

SELECT SkillName 
     , SkillCode 
     , SUM(Beginner)    AS Beginner     
     , SUM(Proficient)  AS Proficient   
     , SUM(Experienced) AS Experienced   
  FROM yourtable
GROUP
    BY SkillName 
     , SkillCode

To understand how this query works, you will need to do some research on two concepts: GROUP BY and aggregate functions, specifically SUM(). Every basic SQL tutorial or textbook will cover these concepts in sufficient detail.

Good luck and welcome to the wonderful world of SQL.