Is there any better way to write this query rather than multiple counts?

1

DECLARE @brand_did INT

SET @brand_did = 101785

SELECT
r2.parameter_desc AS Outcome
, COUNT(CASE WHEN i.portal_status_did = '104' THEN 1 END) [Status:Closed]
, COUNT(CASE WHEN i.portal_status_did = '101502' THEN 1 END) [Status:New]
, COUNT(CASE WHEN i.portal_status_did = '104904' THEN 1 END) [Status:Accepted]
, COUNT(CASE WHEN i.source_did = '101142' THEN 1 END) [Source:Campaign]
, COUNT(CASE WHEN i.source_did = '114' THEN 1 END) [Source:E-mail]
, COUNT(CASE WHEN i.source_did = '105003' THEN 1 END) [Source:Facebook]
, COUNT(CASE WHEN i.source_did = '101908' THEN 1 END) [Source:Microsite]
, COUNT(CASE WHEN i.source_did = '104816' THEN 1 END) [Source:Mobile Device]
, COUNT(CASE WHEN i.source_did = '101911' THEN 1 END) [Source:Motorshow]
, COUNT(CASE WHEN i.source_did = '132' THEN 1 END) [Source:Phone]
, COUNT(CASE WHEN i.source_did = '101906' THEN 1 END) [Source:Skoda Website]
, COUNT(CASE WHEN i.source_did = '101909' THEN 1 END) [Source:Dealer]
, COUNT(*) AS Total
FROM incident i (NOLOCK)
LEFT
OUTER
JOIN reference_parameter_ml r2 (NOLOCK)
ON r2.reference_parameter_did = i.portal_outcome_did
WHERE i.vehicle_purpose_did = @brand_did
GROUP
BY r2.parameter_desc

2

probably not. However I would not use COUNT, the way you have it SQL will give the warning "Aggregate included NULLs" which may well upset applications etc. or mask other, more critical, such errors.

To avoid that issue I would use SUM instead:

...
, SUM(CASE WHEN i.portal_status_did = '104' THEN 1 ELSE 0 END) [Status:Closed]
...

Get rid of that. Terrifies me when i see that in code (if you don't know why then please ask, happy to explain), as developers never seem to know what, and how dire, the consequences are. If you have a problem with Reads blocking Writes then nowadays that is usually most easily solved by setting the database to READ COMMITTED SNAPSHOT

3

For efficiency, don't convert the numeric code to a description until after the grouping. That way you don't have to translate every row, only 1 row per unique code.

SELECT 
r2.parameter_desc AS Outcome
, SUM([Status:Closed]) AS [Status:Closed]
, SUM([Status:New]) AS [Status:New]
, SUM([Status:Accepted]) AS [Status:Accepted]
, SUM([Source:Campaign]) AS [Source:Campaign]
, SUM([Source:E-mail]) AS [Source:E-mail]
, SUM([Source:Facebook]) AS [Source:Facebook]
, SUM([Source:Microsite]) AS [Source:Microsite]
, SUM([Source:Mobile Device]) AS [Source:Mobile Device]
, SUM([Source:Motorshow]) AS [Source:Motorshow]
, SUM([Source:Phone]) AS [Source:Phone]
, SUM([Source:Skoda Website]) AS [Source:Skoda Website]
, SUM([Source:Dealer]) AS [Source:Dealer]
, SUM(Total) AS Total
FROM (
    SELECT
    i.portal_outcome_did
    , SUM(CASE WHEN i.portal_status_did = '104' THEN 1 ELSE 0 END) [Status:Closed]
    , SUM(CASE WHEN i.portal_status_did = '101502' THEN 1 ELSE 0 END) [Status:New]
    , SUM(CASE WHEN i.portal_status_did = '104904' THEN 1 ELSE 0 END) [Status:Accepted]
    , SUM(CASE WHEN i.source_did = '101142' THEN 1 ELSE 0 END) [Source:Campaign]
    , SUM(CASE WHEN i.source_did = '114' THEN 1 ELSE 0 END) [Source:E-mail]
    , SUM(CASE WHEN i.source_did = '105003' THEN 1 ELSE 0 END) [Source:Facebook]
    , SUM(CASE WHEN i.source_did = '101908' THEN 1 ELSE 0 END) [Source:Microsite]
    , SUM(CASE WHEN i.source_did = '104816' THEN 1 ELSE 0 END) [Source:Mobile Device]
    , SUM(CASE WHEN i.source_did = '101911' THEN 1 ELSE 0 END) [Source:Motorshow]
    , SUM(CASE WHEN i.source_did = '132' THEN 1 ELSE 0 END) [Source:Phone]
    , SUM(CASE WHEN i.source_did = '101906' THEN 1 ELSE 0 END) [Source:Skoda Website]
    , SUM(CASE WHEN i.source_did = '101909' THEN 1 ELSE 0 END) [Source:Dealer]
    , SUM(1) AS Total
    FROM incident i (NOLOCK)
    WHERE i.vehicle_purpose_did = @brand_did
    GROUP
    BY i.portal_outcome_did
) AS i_group
LEFT
OUTER
JOIN reference_parameter_ml r2 (NOLOCK)
ON r2.reference_parameter_did = i_group.portal_outcome_did
GROUP
BY r2.parameter_desc