WITH PERSON_NUMS AS (
  SELECT
    "総数" AS PERSON,
    0 AS NUM
  UNION SELECT "１人", 1
  UNION SELECT "２人", 2
  UNION SELECT "３人", 3
  UNION SELECT "４人", 4
  UNION SELECT "５人", 5
  UNION SELECT "６人以上", 6
),
JOINDATA AS (
  SELECT
    CLASS_CODE,
    MAX(AMT * 10 + NUM) / 10 AS AMT,
    MAX(AMT * 10 + NUM) % 10 AS NUM
  FROM
    HOUSEHOLD 
    LEFT JOIN PERSON_NUMS ON HOUSEHOLD.PERSON_NAME = PERSON_NUMS.PERSON
  WHERE
    PERSON_CODE != 1
    AND CLASS_CODE != '01'
  GROUP BY
    CLASS_CODE
),
CLASS_CODE_NAME AS (
  SELECT
    CLASS_CODE,
    CLASS_NAME
  FROM
    HOUSEHOLD
  GROUP BY
    CLASS_CODE,
    CLASS_NAME
)

SELECT
  CLASS_NAME AS `CLASS`,
  `PERSON`,
  AMT AS `HOUSEHOLDS`
FROM
  JOINDATA
  LEFT JOIN CLASS_CODE_NAME ON JOINDATA.CLASS_CODE = CLASS_CODE_NAME.CLASS_CODE
  LEFT JOIN PERSON_NUMS ON JOINDATA.NUM = PERSON_NUMS.NUM
ORDER BY
  JOINDATA.CLASS_CODE