How do I add another column to query that needs to be filtered if I am already filtered for another column in the where clause?

I need help creating a report and I’m not sure if I should something I’ve been researching called a pivot? I have a table "UwrOTRNBaseRates", with 3 columns: State, RateType, and Rate. The report needs to be a column with State, then a column of rates filtered on ‘FMLY’ RateType, then a column of rates filtered on ‘SNGL’ RateType. I only know how to generate the report with either or of the rate type filters. I need help adding both. How do I add a third column for single rate if I’m already filtering on FMLY in the where clause? I built the report below in excel of what I am trying to accomplish in SQL.

SELECT State, Rate as FamilyRate 
FROM UwrOTRNBaseRates
WHERE RateType = 'FMLY'

>Solution :

This can be done using the conditional aggregation using the aggregation function sum() as follows :

SELECT State, sum(CASE WHEN RateType = 'FMLY' THEN 1 ELSE 0 END) AS FAMILY_RATE,
              sum(CASE WHEN RateType = 'SNGL' THEN 1 ELSE 0 END) AS SINGLE_RATE
FROM UwrOTRNBaseRates
GROUP BY State

Or using count() :

SELECT State, count(CASE WHEN RateType = 'FMLY' THEN 1 END) AS FAMILY_RATE,
              count(CASE WHEN RateType = 'SNGL' THEN 1 END) AS SINGLE_RATE
FROM UwrOTRNBaseRates
GROUP BY State