SELECT HH.CLASS_NAME AS CLASS,
HH.PERSON_NAME AS PERSON,
MAIN.HOUSEHOLDS 
FROM (
SELECT
    CLASS_CODE,
    -- CASE WHEN MAX(CLASS_CODE) = '2' THEN '１人'
    --      WHEN MAX(CLASS_CODE) = '3' THEN '２人' 
    --      WHEN MAX(CLASS_CODE) = '4' THEN '３人' 
    --      WHEN MAX(CLASS_CODE) = '5' THEN '４人' 
    --      WHEN MAX(CLASS_CODE) = '6' THEN '５人' 
    --      ELSE '６人以上' END AS PERSON,
    MAX(AMT) AS HOUSEHOLDS
FROM HOUSEHOLD
WHERE (PERSON_CODE <> '1' AND CLASS_CODE <> '01')
GROUP BY CLASS_NAME
) AS MAIN
INNER JOIN HOUSEHOLD AS HH
ON HH.CLASS_CODE = MAIN.CLASS_CODE
AND HH.AMT = MAIN.HOUSEHOLDS
ORDER BY HH.CLASS_CODE ASC
;