SELECT actiontype,
       propertytype,
       loanpurpose,
       count(*) AS num_records
FROM hmda_11
GROUP BY actiontype,
         propertytype,
         loanpurpose;


SELECT actiontype,
       propertytype,
       loanpurpose,
       count(*) AS num_records
FROM hmda_06
GROUP BY actiontype,
         propertytype,
         loanpurpose
ORDER BY actiontype,
         propertytype,
         loanpurpose;


SELECT actiontype,
       propertytype,
       loanpurpose,
       count(*) AS num_records
FROM hmda_07
GROUP BY actiontype,
         propertytype,
         loanpurpose
ORDER BY actiontype,
         propertytype,
         loanpurpose;


SELECT actiontype,
       propertytype,
       loanpurpose,
       count(*) AS num_records
FROM hmda_08
GROUP BY actiontype,
         propertytype,
         loanpurpose
ORDER BY actiontype,
         propertytype,
         loanpurpose;


SELECT actiontype,
       propertytype,
       loanpurpose,
       count(*) AS num_records
FROM hmda_09
GROUP BY actiontype,
         propertytype,
         loanpurpose
ORDER BY actiontype,
         propertytype,
         loanpurpose;


SELECT actiontype,
       propertytype,
       loanpurpose,
       count(*) AS num_records
FROM hmda_10
GROUP BY actiontype,
         propertytype,
         loanpurpose
ORDER BY actiontype,
         propertytype,
         loanpurpose;


SELECT actiontype,
       propertytype,
       loanpurpose,
       count(*) AS num_records
FROM hmda_11
GROUP BY actiontype,
         propertytype,
         loanpurpose
ORDER BY actiontype,
         propertytype,
         loanpurpose;


SELECT sum((loanpurpose = '1'
            AND propertytype IN ('1', '2'))) AS home_purchase,
       sum((loanpurpose = '3'
            AND propertytype IN ('1', '2'))) AS refinance,
       sum((loanpurpose = '2'
            AND propertytype IN ('1', '2'))) AS home_improvement,
       sum((propertytype = '3')) AS multifamily,
       count(*) AS total
FROM hmda_11
WHERE actiontype = '1';


SELECT sum((loanpurpose = '1'
            AND propertytype IN ('1', '2'))) AS home_purchase,
       sum((loanpurpose = '3'
            AND propertytype IN ('1', '2'))) AS refinance,
       sum((loanpurpose = '2'
            AND propertytype IN ('1', '2'))) AS home_improvement,
       sum((propertytype = '3')) AS multifamily,
       count(*) AS total
FROM hmda_06
WHERE actiontype = '1'
UNION
SELECT sum((loanpurpose = '1'
            AND propertytype IN ('1', '2'))) AS home_purchase,
       sum((loanpurpose = '3'
            AND propertytype IN ('1', '2'))) AS refinance,
       sum((loanpurpose = '2'
            AND propertytype IN ('1', '2'))) AS home_improvement,
       sum((propertytype = '3')) AS multifamily,
       count(*) AS total
FROM hmda_07
WHERE actiontype = '1'
UNION
SELECT sum((loanpurpose = '1'
            AND propertytype IN ('1', '2'))) AS home_purchase,
       sum((loanpurpose = '3'
            AND propertytype IN ('1', '2'))) AS refinance,
       sum((loanpurpose = '2'
            AND propertytype IN ('1', '2'))) AS home_improvement,
       sum((propertytype = '3')) AS multifamily,
       count(*) AS total
FROM hmda_08
WHERE actiontype = '1'
UNION
SELECT sum((loanpurpose = '1'
            AND propertytype IN ('1', '2'))) AS home_purchase,
       sum((loanpurpose = '3'
            AND propertytype IN ('1', '2'))) AS refinance,
       sum((loanpurpose = '2'
            AND propertytype IN ('1', '2'))) AS home_improvement,
       sum((propertytype = '3')) AS multifamily,
       count(*) AS total
FROM hmda_09
WHERE actiontype = '1'
UNION
SELECT sum((loanpurpose = '1'
            AND propertytype IN ('1', '2'))) AS home_purchase,
       sum((loanpurpose = '3'
            AND propertytype IN ('1', '2'))) AS refinance,
       sum((loanpurpose = '2'
            AND propertytype IN ('1', '2'))) AS home_improvement,
       sum((propertytype = '3')) AS multifamily,
       count(*) AS total
FROM hmda_10
WHERE actiontype = '1'
UNION
SELECT sum((loanpurpose = '1'
            AND propertytype IN ('1', '2'))) AS home_purchase,
       sum((loanpurpose = '3'
            AND propertytype IN ('1', '2'))) AS refinance,
       sum((loanpurpose = '2'
            AND propertytype IN ('1', '2'))) AS home_improvement,
       sum((propertytype = '3')) AS multifamily,
       count(*) AS total
FROM hmda_11
WHERE actiontype = '1';

SELECT race,
       count(*)
FROM hmda_11
WHERE actiontype = '1'
  AND loanpurpose = '1'
  AND occupancy = '1'
  AND lienstatus = '1'
  AND propertytype IN ('1',
                       '2')
GROUP BY race
ORDER BY race;


SELECT ethnicity,
       count(*)
FROM hmda_11
WHERE actiontype = '1'
  AND loanpurpose = '1'
  AND occupancy = '1'
  AND lienstatus = '1'
  AND propertytype IN ('1',
                       '2')
  AND race = '5'
GROUP BY ethnicity
ORDER BY ethnicity;