How to make a sql view where each row is the result of a query?

user_11967073 0
Viewed 52

I am currently working on PostgreSQL version 14. I got two tables, one is a list of emails, the other one describe if those emails are invalid or was marked as unsubscribe (black_list). I want to make the percentage of how many addresses are invalid and unsubscribed in two different rows in the same table using a view.

My email table

| email_id | email|
|:---- |:------:|
| 1| bob.smith@yahoo.com|
| 2| ronald.gregor@gmail.com|
| 3| jo123456@gmail.com|

My table black_list looks like that.

email_id unsubscribe invalid
1 True False
3 False True

The result I expect.

categories value
unsubscribe 33
invalid 33

I tried to make a view with this query :

CREATE OR REPLACE VIEW percentage_unsubscribe (value) AS SELECT (SELECT COUNT(*) 
FROM black_list WHERE unsubscribe = True)/(SELECT COUNT(*) FROM email_table

But i would like to know how to pass the categorical column and the second row.

view postgresql sql
1 Answers

Use union to generate two rows and the with statement to optimize the query a bit and make it more readable, e.g.:

create or replace view percentage_unsubscribe (category, value) as 
    with totals as (
        select
            count(*) filter (where unsubscribe) as unsubscribe,
            count(*) filter (where invalid) as invalid,
            (select count(*) from email_table) as total
        from black_list
    )
    select 'unsubscribe', unsubscribe* 100/ total
    from totals
    union
    select 'invalid', invalid* 100/ total
    from totals;
user_19957380
Related