How to form mysql query on a table to group by a column named 'type' and count of another column

Question

I have table with a column named 'Type' and another column 'Result' that has values as 's' and 'f'.

<table border="1" width="100%">
            <tr>
                <td >Type</td>
                <td >Result</td>
            </tr>
            <tr>
                <td>t1</td>
                <td>s</td>
            <tr>
            </tr>
                <td>t2</td><td>s</td>
            <tr>
            </tr>
                <td>t1</td><td>f</td>
            <tr>
            </tr>
                <td>t1</td><td>f</td>
                
            </tr>
        </table>

I want the result like

<table border="1" width="100%">
            <tr>
                <td >Type</td>
                <td >S Count</td>
                <td >F Count</td>
            </tr>
            <tr>
                <td>t1</td>
                <td>1</td>
                <td>2</td>
            <tr>
            </tr>
                <td>t2</td>
                <td>1</td>
                <td>0</td>
            <tr>
            
        </table>

So the First column is distinct of 'type' column. Second and third column will come from the 'Result' column. Count will be added to S Count if Type1 value is 's' otherwise to F count like wise.

How to form the query like below,

select type,result,count(id) from test_table group by type,result;

This one will give the result as 2 separate rows for each type, but i want like given above expected result.

Answer 1

Use conditional aggregation:

SELECT
    Type,
    SUM(Result = 's') AS s_count,
    SUM(Result = 'f') AS f_count
FROM yourTable
GROUP BY
    Type;

Demo