Query count & sum nested selects into a Grouped Category

My current project/issue is we have a list of permit types. With these types they are needing to be grouped by an overall category. Once grouped out the end goal is to count how many within specified date range along with a sum total of the estimated value. The initial group category element doesn't exist.

I'm trying to learn how to use nested select statements, and thought I might be able to do that for each grouped category. Although I can technically get it to work, all the resulted data displayed is on one single row. I figured I'm probably going to need to create a temporary table, but I don't fully understand how it works, and how I would apply it to my query.

declare @CATEGORY1 nvarchar(40)
declare @CATEGORY2 nvarchar(40)
declare @CATEGORY3 nvarchar(40)
declare @CATEGORY4 nvarchar(40)
declare @CATEGORY5 nvarchar(40)
declare @CATEGORY6 nvarchar(40)

Set @CATEGORY1 = 'Commercial - New'
Set @CATEGORY2 = 'Commercial - Modifications'
Set @CATEGORY3 = 'Multi-Family - New'
Set @CATEGORY4 = 'Multi-Family - Modifications'
Set @CATEGORY5 = 'Residential - New'
Set @CATEGORY6 = 'Residential - Modifications'

select 
@CATEGORY4 as 'Group Category'

, query1.[Count]
, query1.[SUM]

,@CATEGORY5 as 'Group Category'
, query2.[Count]
, query2.[SUM]

from 
(
Select
    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' 
) as query1 , 

(
Select
    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' 
) as query2 ;

The results of the above looks like this:

What my end goal is something like this:

You would greatly benefit from using a lookup table.

This allows you to reduce a bulky query full of hardcoded conditions to a simple and maintainable JOIN.

For example, create a table like this:

CREATE TABLE PermitCategoryLookup (
    PermitTypeMasterID INT PRIMARY KEY,
    GroupCategory VARCHAR(50)
);

...and then save your lookup data there...

INSERT INTO PermitCategoryLookup (PermitTypeMasterID, GroupCategory) VALUES
(57, 'Commercial - New'),
(60, 'Commercial - New'),
(59, 'Commercial - New'),

(1, 'Commercial - Modifications'),
(46, 'Commercial - Modifications'),
(78, 'Commercial - Modifications'),
(79, 'Commercial - Modifications'),

(100, 'Multi-Family - New'),
(101, 'Multi-Family - New'),

(102, 'Multi-Family - Modifications'),
(103, 'Multi-Family - Modifications'),

(200, 'Residential - New'),
(201, 'Residential - New'),

(202, 'Residential - Modifications'),
(203, 'Residential - Modifications');

After that, the final query becomes straightforward:

SELECT
    pcl.GroupCategory,
    COUNT(DISTINCT p.PermitNum) AS Count,
    SUM(p.EstimatedValue) AS SUM
FROM Permit p
JOIN PermitCategoryLookup pcl ON p.PermitTypeMasterID = pcl.PermitTypeMasterID
WHERE p.CreatedDate >= '2025-01-01'
GROUP BY pcl.GroupCategory;

If the categorization changes later, you can simply update the lookup data - potentially even via a UI - without touching the query or source code.

Without a lookup table, every change requires editing the query itself, which quickly becomes messy, error-prone and hard to maintain due to long CASE expressions or chains of UNION ALL.

See this db<>fiddle with sample data. It shows how much cleaner this approach is compared to CASE or UNION ALLlogic.