SQL Server : select the total sum of all the models from each agency and select the count of each model
<p>I am trying to get the total sum of all the models from each agency and select the count of each model.</p>

<p>We have different tables in a database of cars agency, I have the next query:</p>

<pre><code>SELECT distinct (c.check) ,cm.description as agency, cms.description as model, co.result
FROM check c 
INNER JOIN check_obd co on c.idcheck=co.idcheck
INNER JOIN vehicular_census vc on vc.idvehicularcensus = c.idvehicularcensus
LEFT JOIN cat_mark cm on vc.idmark=cm.key
LEFT JOIN cat_mark_submark cms on vc.idmark_submark=cms.idmark_submark
WHERE  c.operation_status=5
</code></pre>

<p>This query is not ideal as it produces:</p>

<p><img src="https://i.stack.imgur.com/vxfvA.png" alt="Before"></p>

<p>I would like the result to look like this:</p>

<p><img src="https://i.stack.imgur.com/R4ncE.png" alt="After"></p>

<p>How can I modify the above query to get my desired result?</p>