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
```