Query count & sum nested selects into a Grouped Category

You are on the right track with using subqueries to group and aggregate your data but the issue is that you are selecting both subqueries side by side which gives you one wide row with multiple columns instead of multiple rows (one per group).

What you want is rows per category, which you can achieve using UNION ALL.

SELECT 
    'Multi-Family - Modifications' AS [Group Category],
    COUNT(DISTINCT P1.PermitNum) AS [Count],
    SUM(P1.EstimatedValue) AS [SUM]
FROM Permit P1
WHERE P1.PermitTypeMasterID IN (57, 60, 59)
  AND P1.CreatedDate >= '2025-01-01'

UNION ALL

SELECT 
    'Residential - New' AS [Group Category],
    COUNT(DISTINCT P2.PermitNum) AS [Count],
    SUM(P2.EstimatedValue) AS [SUM]
FROM Permit P2
WHERE P2.PermitTypeMasterID IN (1, 46, 78, 79)
  AND P2.CreatedDate >= '2025-01-01'

The union all approach is a nice and quick way to get your results with the least modifications to your presented query. However, if you have to do it on 6 categories, and have more complex computations to do on your data (but want the same computations for all categories), you'll risk copy-pasting errors and should probably convert your PermitTypeMasterID to a category then group by those categories.

In addition to the code mutualization (same WHERE filter, same SUM…) you'll get the benefit of an "other" category to show PermitTypeMasterID values that you would have forgotten; and the possibility to order (by decrementing SUM for example

Your query would then look like:

Select
    Case
        When P.PermitTypeMasterID in (57,60,59) Then 'Multi-Family - Modifications'
        When P.PermitTypeMasterID in (1, 46, 78, 79) Then 'Residential - New'
        …
        Else 'Others'
    End AS [Group Category],
    Count(DISTINCT P.PermitNum) as 'Count'
    , Sum(P.EstimatedValue) as 'SUM'
FROM
    Permit P
WHERE
P.CreatedDate >= '2025-01-01' 
GROUP BY <first column of result>;

Alas, SQL Server doesn't allow this GROUP BY <first column of result>, so you'll have to either:

1. repeat the Case in the GROUP BY
2. first add the category to each row (thanks to a subquery or Common Table Expression
3. or use a reference table

In either case you'll get the expected:

You'll find the 3 possibilities under this db<>fiddle, and here I'll detail 2. and 3.:

2. With a CTE

A CTE (Common Table Expression) is like an evanescent table that lasts (and is visible) only for your query. Like the temporary tables your spoke of, but with even less side effects.

The simplest would be:

With PermitAndCategory As (Select *, Case When … End AS [Group Category] FROM Permit P)
Select [Group Category], Count(DISTINCT P.PermitNum) as 'Count', Sum(P.EstimatedValue) as 'SUM'
From PermitAndCategory P
WHERE P.CreatedDate >= '2025-01-01'
GROUP BY [Group Category];

However, like a temporary table, it has to store its results server-side, so unless you have a small dataset we'd better:

• not use * but select only the columns that will be used in the main, final Select
• filter as soon as possible
• group as soon as possible (at least we can pre-GROUP BY PermitTypeMasterID)

to alleviate the database server's memory use, and its final GROUP BY (a CTE is not indexed, so the less it has rows, the quicker the final query will be):

With PermitAndCategory As
(
    Select
        Case
            When P.PermitTypeMasterID in (57,60,59) Then 'Multi-Family - Modifications'
            When P.PermitTypeMasterID in (1, 46, 78, 79) Then 'Residential - New'
            -- …
            Else 'Others'
        End AS [Group Category],
        Count(DISTINCT P.PermitNum) as 'Count'
        , Sum(P.EstimatedValue) as 'SUM'
    FROM Permit P
    WHERE P.CreatedDate >= '2025-01-01'
    GROUP BY P.PermitTypeMasterID
)
Select [Group Category], Sum([Count]) as [Count], Sum([SUM]) as [SUM]
From PermitAndCategory
Group By [Group Category];

3. Use a reference table

In addition to ease grouping (you just have to group by [Group Category] after having joined to the reference table mapping each PermitTypeMasterID to its [Group Category]), you will ease your maintenance when adding new values of PermitTypeMasterID: instead of 2 medias (one insert to Permit, one text script to deploy), you'll have one (inserts to 2 tables of the same database).

create table MasterCategory
(
    PermitTypeMasterID int,
    [Group Category] varchar(99)
    constraint PermitTypeMasterIDUnique Unique(PermitTypeMasterID)
);
insert into MasterCategory values
    (57, 'Multi-Family - Modifications'),
    (59, 'Multi-Family - Modifications'),
    (60, 'Multi-Family - Modifications'),
    (1, 'Residential - New'),
    (46, 'Residential - New'),
    (78, 'Residential - New'),
    (79, 'Residential - New'),
    …;

Select
    MC.[Group Category],
    Count(DISTINCT P.PermitNum) as 'Count'
    , Sum(P.EstimatedValue) as 'SUM'
FROM Permit P Left Join MasterCategory MC On MC.PermitTypeMasterID = P.PermitTypeMasterID -- Left Join, in case we have forgotten to insert a value to MasterCategory.
WHERE P.CreatedDate >= '2025-01-01'
GROUP BY MC.[Group Category]
;

(and in fact you could even have a reference table for categories, whose key would be an integer referenced from MasterCategory, to avoid typing errors in MasterCategory)

I would recommend this solution that puts all your data and metadata in the same database, not relying on your SQL script that is maintained apart and could get lost independently of the database.