Conditional Aggregation with SUM and COUNT combined (different columns)

Question

I have a table:

State      City     Class   Population      
 S1         SC1       A         10
 S1         SC2       B         5
 S1         SC3       A         7
 S2         S2C1      B         6
 S2         S2C2      A         13
 S2         S2C3      A         10

The desired result set from query:

States     Count_of_Class_A_Cities     Total Population of State
  S1                 2                           22
  S2                 2                           29

Is this possible to do using conditional sum and count aggregates? or pivots? and not using inner count or inner sum statements within a select statement.

Thanks.

Answer 1

    SELECT
        State AS States ,
        COUNT(CASE WHEN Class = 'A' THEN 1 END) AS Count_of_Class_A_Cities ,
        SUM(Population) AS Total_Population_of_State
FROM TABLE

    GROUP BY
        State 

Answer 2

Use case when counting A cities only:

   select state,
          sum(case when class = 'A' then 1 else 0 end) as Count_of_Class_A_Cities,
          sum(Population) as Total_Population_of_State
   from tablename
   group by state