Oracle Query combining Two tables

Question

I have two tables. I want to combine this tables to get the results like below:

Table_1

RegNo       Class_id    Name    Address
------------------------------------------------
ABC/R/13-14-1       1   Name1   Address1
ABC/R/13-14-2       2   Name1   Address1
ABC/R/2014-15-1     1   Name1   Address1
ABC/R/2014-15-2     3   Name1   Address1
ABC/R/13-14-3       1   Name1   Address1
------------------------------------------------

Table_2

Class_id    Class
----------------------
1       IA
2       IB
3       IC
----------------------

i need the result like:

Class   2013    2014
--------------------
IA  2   1
IB  1   0
IC  0   1
--------------------

I am using Group by to get the results in a separately for each year, but I am not able to get the year in column-wise format like the requirement. Any help is highly appreciated.

Thanks in advance.

Answer 1

If condition you used in your query works for your data then you can use this query:

select t2.class, 
    sum(case when regno like 'ABC%13-14%' then 1 else 0 end) c2013,
    sum(case when regno like 'ABC%14-15%' then 1 else 0 end) c2014
  from table_1 t1
  join table_2 t2 on t1.class_id = t2.class_id
  group by t2.class

Output:

CLASS      C2013      C2014
----- ---------- ----------
IA             2          1
IB             1          0
IC             0          1