CODEWITHSUNDEEP
sqlite
Gulbahar
Gulbahar

Reputation: 5537

Compute percentage from summed columns?

I'm not sure how to write the SQL query for this.

Here's the table:

ZIP;NUMBER
01800;456
02850;445
75001;123
75002;456
75003;789

I need to 1. sum the NUMBER column for all rows where ZIP starts with "75", and 2. compute the percentage those rows represent over SUM(NUMBER).

Something like that:

select SUM(NUMBER) from MYTABLE WHERE ZIP LIKE "75%" AS 75ZIP;
select (75ZIP/SUM(NUMBER)*100) from MYTABLE;

Should I use GROUP BY HAVING, a sub query, or something else entirely?

Thank you.

Upvotes: 0

Views: 36

Answers (2)

Shawn
Shawn

Reputation: 52539

For future reference, as an alternative, the upcoming 3.30 release supports filtering aggregate functions like can already be done with window functions:

SELECT 100.0 * sum(number) FILTER (WHERE zip LIKE '75%') / sum(number)
FROM mytable;

Upvotes: 0

forpas
forpas

Reputation: 164139

With conditional aggregation:

select 
  100.0 * sum(case when zip like '75%' then number else 0 end) / sum(number)
from tablename

Upvotes: 2

Related Questions