Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- bare minimum table
CREATE TABLE surveys (
  surveyid int PRIMARY KEY
, descriptor text
, accuracy_of_bill_yn text  -- should be boolean
, cleanliness_yn text
, service_yn text
, value_yn text
);

INSERT INTO surveys VALUES
  (1, 'Fast Food', 'Yes', 'Yes', 'No' , 'No')
, (2, 'Boutique' , 'No' , 'No' , 'Yes', 'Yes')
, (3, 'Bar'      , 'No' , 'Yes', 'Yes', 'No')
, (4, 'Eatery'   , 'No' , 'Yes', 'No' , 'No')
, (5, 'Bar'      , 'No' , 'Yes', 'Yes', 'Yes')  -- excluded below
;

CREATE TABLE excluded (
  surveyid int PRIMARY KEY
);

INSERT INTO excluded VALUES
  (5)
;

CREATE TABLE



INSERT 0 5



CREATE TABLE



INSERT 0 1


-- base query
SELECT CASE descriptor
         WHEN 'Fast Food'    THEN 'Base'
         WHEN 'Sit Down'     THEN 'Premium'
         WHEN 'Bar'          THEN 'Premium'
         WHEN 'Eatery'       THEN 'Premium'
         WHEN 'Full Service' THEN 'Deluxe'
         WHEN 'Boutique'     THEN 'Deluxe'
       END AS brand_group
     , count(*) FILTER (WHERE accuracy_of_bill_yn = 'Yes') AS a_ct
     , count(*) FILTER (WHERE cleanliness_yn = 'Yes')      AS c_ct
     , count(*) FILTER (WHERE service_yn = 'Yes')          AS s_ct
     , count(*) FILTER (WHERE value_yn = 'Yes')            AS v_ct
FROM   surveys s
WHERE  NOT EXISTS (SELECT FROM excluded e WHERE e.surveyid = s.surveyid)
/*  -- not in my minimum demo
AND  responsedate >= now() - interval '12 months'
AND    region IN (1,2,3,4,5)
AND    brand_group IS NOT NULL
*/
GROUP  BY 1;


brand_group
a_ct
c_ct
s_ct
v_ct


Base
1
1
0
0

Deluxe
0
0
1
1

Premium
0
2
1
0



SELECT 3


-- "unpivoted"
SELECT brand_group, p.*
FROM  (
   SELECT CASE descriptor
            WHEN 'Fast Food'    THEN 'Base'
            WHEN 'Sit Down'     THEN 'Premium'
            WHEN 'Bar'          THEN 'Premium'
            WHEN 'Eatery'       THEN 'Premium'
            WHEN 'Full Service' THEN 'Deluxe'
            WHEN 'Boutique'     THEN 'Deluxe'
          END AS brand_group
        , count(*) FILTER (WHERE accuracy_of_bill_yn = 'Yes') AS a_ct
        , count(*) FILTER (WHERE cleanliness_yn = 'Yes')      AS c_ct
        , count(*) FILTER (WHERE service_yn = 'Yes')          AS s_ct
        , count(*) FILTER (WHERE value_yn = 'Yes')            AS v_ct
   FROM   surveys s
   WHERE  NOT EXISTS (SELECT FROM excluded e WHERE e.surveyid = s.surveyid)
   /*  -- not in my minimum demo
   AND    responsedate >= now() - interval '12 months'
-- AND    surveyid NOT IN (SELECT surveyid FROM surveys)  -- ?? nonsense
   AND    region IN (1,2,3,4,5)
   AND    brand_group IS NOT NULL
   */  
   GROUP  BY 1
   ) sub
CROSS  JOIN LATERAL (
   VALUES
     ('Accuracy of bill', a_ct)
   , ('Cleanliness'     , c_ct)
   , ('Service'         , s_ct)
   , ('Value'           , v_ct)
   ) p(base, count)
ORDER  BY 1, 2;  -- need that?


brand_group
base
count


Base
Accuracy of bill
1

Base
Cleanliness
1

Base
Service
0

Base
Value
0

Deluxe
Accuracy of bill
0

Deluxe
Cleanliness
0

Deluxe
Service
1

Deluxe
Value
1

Premium
Accuracy of bill
0

Premium
Cleanliness
2

Premium
Service
1

Premium
Value
0



SELECT 12
brand_group	base	count
Base	Accuracy of bill	1
Base	Cleanliness	1
Base	Service	0
Base	Value	0
Deluxe	Accuracy of bill	0
Deluxe	Cleanliness	0
Deluxe	Service	1
Deluxe	Value	1
Premium	Accuracy of bill	0
Premium	Cleanliness	2
Premium	Service	1
Premium	Value	0