How to add ‘all’ CASE when group by CASES in SQL

I have a query like this:

select
case when  sp.provider='prv1' and    sp.status_code = 404 then 'failed'
     when  sp.provider='prv1' and    sp.status_code = 200 then 'success'
     when  time_taken is null or sp.status_code in (503) or sp.status_code is null then  'unavailable' 
     else 'other'
    end as result,
    count(*)
from services_serviceprofile sp
where 
service_type = 'type1' and
sp.provider = 'prv1' and 
sp.start_time >= '2022-08-22 00:00'
group by 
  case when  sp.provider='prv1' and    sp.status_code = 404 then 'failed'
         when  sp.provider='prv1' and    sp.status_code = 200 then 'success'
         when  time_taken is null or      sp.status_code = 503  or sp.status_code is null then  'unavailable' 
         else 'other'
    end 
order by count(*) desc;

Which gives the result:

How can I add a row for ‘all’ requests in CASES? If we add a when sp.provider='prv1' with no more detailed condition then all cases become one ‘all’ case because and other cases are ignored.

Answer

You can’t do that inside the CASE, as it behaves like a series of IF.

You need to compute the total separately and append it using UNION or delegate the total calculation to whatever tool uses those data.

WITH RequestStats
AS (
     SELECT
           CASE 
               WHEN sp.provider = 'prv1' AND sp.status_code = 404 THEN 'failed'
               WHEN sp.provider = 'prv1' AND sp.status_code = 200 THEN 'success'
               WHEN time_taken IS NULL OR sp.status_code IN (503) OR sp.status_code IS NULL THEN 'unavailable'
               ELSE 'other'
               END AS Result
           ,count(*) AS RequestCount
     FROM services_serviceprofile sp
     WHERE
          service_type = 'type1'
          AND sp.provider = 'prv1'
          AND sp.start_time >= '2022-08-22 00:00'
)

SELECT 
      Result
     ,RequestCount
FROM RequestStats

UNION ALL

SELECT 
     'All' AS Result
     ,SUM(RequestCount) AS RequestCount
FROM RequestStats