PostgreSQl addition on error on a case expression and from calculating a percentage from a from subquery

Question

I'm writing a postgreSQL query that has to calculate the cost of sheltering animals taking into account, the size, age and location of the animal.

EDIT

SELECT 
    lower(a.animaltype) as animaltype,
    a.size,
    SUM(a.total) AS total,
    round((SUM(a.total) * 100 / 2263360),2) AS percentage
FROM
(SELECT 
    animals.animaltype,
    CASE 
        WHEN animals.animaltype = 'Dog' AND weight > 30
            THEN 'large'
        WHEN animals.animaltype = 'Dog' AND weight <= 10 
            THEN 'small'
        WHEN animals.animaltype = 'Dog' AND weight BETWEEN  10 AND 30
            THEN 'medium'
        WHEN animals.animaltype = 'Cat' AND weight > 7 
            THEN 'large'
        WHEN animals.animaltype = 'Cat' AND weight <= 5 
            THEN 'small'
        WHEN animals.animaltype = 'Cat' AND weight BETWEEN 5 AND 7 
            THEN 'medium'
        WHEN animals.animaltype = 'Bird' AND weight > 1.1 
            THEN 'large'
        WHEN animals.animaltype = 'Bird' AND weight <= 0.7 
            THEN 'small'
        WHEN animals.animaltype = 'Bird' AND weight BETWEEN 0.7 AND 1.1
            THEN 'medium'
    END AS size,
    SUM
        (CASE
            WHEN animals.animaltype = 'Dog' AND weight > 30
                THEN 175
            WHEN animals.animaltype = 'Dog' AND weight <= 10 
                THEN 125
            WHEN animals.animaltype = 'Dog' AND weight > 10 AND weight <= 30 
                THEN 150
            WHEN animals.animaltype = 'Cat' AND weight > 7 
                THEN 140
            WHEN animals.animaltype = 'Cat' AND weight <= 5 
                THEN 100
            WHEN animals.animaltype = 'Cat' AND weight > 5 AND weight <= 7 
                THEN 120
            WHEN animals.animaltype = 'Bird' AND weight > 1.1 
                THEN 110
            WHEN animals.animaltype = 'Bird' AND weight <= 0.7 
                THEN 70
            WHEN animals.animaltype = 'Bird' AND weight > 0.7 AND weight <= 1.1
                THEN 90
        END)
    +
    SUM(
        CASE 
        WHEN (DATE_PART('YEAR','2021-12-31'::DATE) - DATE_PART('year', animals.birthdate::DATE)) > 4
            THEN 100
        WHEN (DATE_PART('YEAR','2021-12-31'::DATE) - DATE_PART('year', animals.birthdate::DATE)) > 7
            THEN 150
        WHEN (DATE_PART('YEAR','2021-12-31'::DATE) - DATE_PART('year', animals.birthdate::DATE)) > 12
            THEN 200
        WHEN (DATE_PART('YEAR','2021-12-31'::DATE) - DATE_PART('year', animals.birthdate::DATE)) > 15
            THEN 300
        ELSE 
             0
        END)
        
    +
    SUM(
         CASE
            WHEN animals.location = 'San Antonio'
                THEN 120
            WHEN animals.location = 'Dallas'
                THEN 110
            WHEN animals.location = 'Houston'
                THEN 140
            WHEN animals.location = 'Austin'
                THEN 135
            WHEN animals.animaltype = 'Fort Worth'
                THEN 100

        END)
    AS total
FROM animals
LEFT JOIN sponsored_animals ON animals.animalid = sponsored_animals.sponsorid
WHERE sponsorid IS NULL
GROUP BY animals.animaltype, size
ORDER BY animals.animaltype, size DESC) as a
GROUP BY a.animaltype, size
ORDER BY a.animaltype, size DESC

The data has been cleanup and the are no null values on the tables, except for those animals without sponsor. therefore the WHERE sponsorid IS NULL clause.

Now, I'm looking for a way get rid of the hard coded 2263360 and simplify the case statements.

Answer 1

In the addition, the last case (the location) has no else case. If an animal is in a different city than the listed ones, this case leads to a null value, the entire addition is null for this record and this record is therefore ignored from the sum