How to add 'all' CASE when group by CASES in SQL-CodePudding

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.

CodePudding user response：

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