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

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| [email protected]|
| 2| [email protected]|
| 3| [email protected]|

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.

CodePudding user response：

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;