Better optimized version of the below SQL

Question

Currently I have created this SQL query:

select "Col1",
       "Col2",
       max(Col3) as "Col3"
  from (select Col1, Col2, 0 as "Col3" 
          from table1
         where col1='abc'

        union

        select Col1, Col2, count(Col3) as "Col3"
          from table1,
              (select table1.col1, count(table2.Col3) 
                 from table1, table2 
                where table1.col1 = table2.col1 
                group by table1.col1) 
      ) All_Records
where ALL_Records.Col1 = 'abc'
group by "Col1", "Col2"

Is there a better way to optimize the sql select statement, I want to get the max value because if I don't use I would end up with 2 rows for same row with 0 (hardcoded) and the other value returned by the logic.

Problem Statement: I want to retrieve the number of times the salary has changed for the employees, I am using the union to get the records where there is entry in SALARY_HISTORY table, if there are no rows in SALARY_HISTORY display 0.

select "EMPID",max(SALARY_CHANGE_RECORDS.Salary_Change_Count)
    select EMP.ID as "EMPID",SALARY_CHANGE.Change_Count as "Salary_Change_Count"
    from 
    EMP,
    (select EMP.ID, COUNT(SALARY.Salary_Change_ID) as "Salary_Change_Count"
        from EMP, SALARY_HISTORY  where EMP.ID=SALARY_HISTORY.ID group by EMP.ID
    union 
    select EMP.ID, 0 as "Change_Count"from EMP) SALARY_CHANGE_RECORDS
group by "EMPID" 

Answer 1

It seems to me that this would work:

select    emp.id,
          count(salary_history.Salary_Change_ID)
from      emp
left join salary_history on emp.id = salary_history.id
group by  emp.id

It just counts the number of salary_history records.