Sum the result under Group By Condition

Question

I have table like below in SQL Server.

Process_ID	Process_Type
011	P1
012	P1
013	P2
014	P2
015	P3
016	P3

I want to get the count of Process ID for the process type P2 and P3 together. I have used query like below:

select count(Process_ID) as Process_Count, Process_Type
from Process_Table
where Process_type in ('P2','P3')
group by Process_Type;

The result shows:

Process_Count	Process_Type
2	P2
2	P3

But I need like:

Process_Count	Process_Type
4	P2_and_P3

Can someone help me how I can sum the result which was used under SQL in condition and group by clause and display it in the output?

Thank you in advance.

Does this answer your question? [string_agg for sql server pre 2017](https://stackoverflow.com/questions/49361088/string-agg-for-sql-server-pre-2017) — Thom A, Jun 21 '21 at 08:32
Does this answer your question? [Comma separated results in SQL](https://stackoverflow.com/questions/18870326/comma-separated-results-in-sql) — Thom A, Jun 21 '21 at 08:32

score 2 · Accepted Answer · answered Jun 21 '21 at 08:36

If you really just want that one-record result set, then drop the GROUP BY clause:

SELECT COUNT(Process_ID) AS Process_Count, 'P2_and_P3' AS Process_Type
FROM yourTable
WHERE Process_Type IN ('P2', 'P3');

If instead you perhaps want to view all process type groups, but with P2 and P3 bucketed together, then aggregate using a CASE expression:

SELECT
    CASE WHEN Process_Type IN ('P2', 'P3')
         THEN 'P2_and_P3' ELSE Process_Type END AS Process_Type,
    COUNT(Process_ID) AS Process_Count
FROM yourTable
GROUP BY
    CASE WHEN Process_Type IN ('P2', 'P3') THEN 'P2_and_P3' ELSE Process_Type END;

Sum the result under Group By Condition

1 Answers1