Percentage calculation in SQL

I have some cars in a table along with the year they came out, and I’m trying to calculate the percentages of the cars that belong in the 4 following age groups: 0-4, 5-9, 10-19, 20+.

I tried to do this in 3 different ways:

1.

Select (Select(Count(*) from public."Vehicles".first_year where first_year between 2018 and 2021)* 100 / (Select Count(*) From public."Vehicles")) as Percentage
From public."Vehicles";

I got this error:

ERROR:  syntax error at or near "from"
LINE 1: Select (Select(Count(*) from public."Vehicles".first_year wh...

SELECT SUM(CASE WHEN public."Vehicles".first_year > 2017 AND public."Vehicles".first_year < 2022 THEN 1 ELSE 0 END) AS ['4 and under'],`
        SUM(CASE WHEN public."Vehicles".first_year > 2012 AND public."Vehicles".first_year < 2018 THEN 1 ELSE 0 END) AS ['5-9'],
        SUM(CASE WHEN public."Vehicles".first_year > 2002 AND public."Vehicles".first_year < 2013 THEN 1 ELSE 0 END) AS ['10-19'],
        SUM(CASE WHEN public."Vehicles".first_year < 2003 THEN 1 ELSE 0 END) AS ['20 and older']
 FROM public."Vehicles";

I got this error:

ERROR:  syntax error at or near "["
LINE 1: ...Vehicles".first_year < 2022 THEN 1 ELSE 0 END) AS ['4 and un...

ROUND(CAST(((Count(*) from public."Vehicles".first_year where first_year between 2018 and 2021) * 100.0 / (Select Count(*) From public."Vehicles")) AS FLOAT), 2) AS Percentage;

I got this error:

ERROR:  syntax error at or near "["
LINE 1: ...Vehicles".first_year < 2022 THEN 1 ELSE 0 END) AS ['4 and un...

Any ideas on what I could do? I’m using pgadmin4 and PostgreSQL 13

Answer

Give your columns more reasonable names! Then for the calculation:

SELECT AVG(CASE WHEN v.first_year > 2017 AND v.first_year < 2022 THEN 1.0 ELSE 0 END) AS avg_4_and_under,
       AVG(CASE WHEN v.first_year > 2012 AND v.first_year < 2018 THEN 1.0 ELSE 0 END) AS avg_5_9,
       AVG(CASE WHEN v.first_year > 2002 AND v.first_year < 2013 THEN 1.0 ELSE 0 END) AS avg_10_19,
       AVG(CASE WHEN v.first_year < 2003 THEN 1.0 ELSE 0 END) AS avg_20_and_older
FROM public."Vehicles" v;

Note the changes to the query:

• The column aliases are simple identifiers and do not need to be escaped. Highly recommended.
• The calculation uses AVG() to get a ratio rather than SUM() for a count. That is what your question claims you want.
• Table aliases make the query easier to write and to read. Hence the v.