Question 113856 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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:
query 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.

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    If you are on SQL Server 2008 or later version, you can use the ROLLUP() GROUP BY function for add last row total:

    select results = ISNULL(result, 'All'), Counts = sum(Counts)  from (
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 Counts
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 
) as Final
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search