CODEWITHSUNDEEP
postgresql
Shezan Kazi
Shezan Kazi

Reputation: 4660

PostgreSQL view with filtered columns

i have a table like this:

date_added        owner        action
01-02-2016        1            note
04-02-2016        1            call
04-02-2016        1            call
05-02-2016        1            note
05-02-2016        1            meeting
06-02-2016        1            meeting
06-02-2016        1            note
06-02-2016        1            cal
06-02-2016        1            note
10-02-2016        1            call
10-02-2016        1            note
10-02-2016        1            meeting

I need a view like this:

date_added        owner        note        call        meeting
01-02-2016        1            1           0           0
04-02-2016        1            0           2           0
05-02-2016        1            0           0           1
06-02-2016        1            2           1           1
10-02-2016        1            1           1           1

How do i create a column with something like

WHERE action LIKE 'note'

?

Upvotes: 0

Views: 40

Answers (1)

Ullas
Ullas

Reputation: 11556

You could use CASE expression.

Query

select date_added, owner,
sum(case action when 'note' then 1 else 0 end) note,
sum(case action when 'call' then 1 else 0 end) call,
sum(case action when 'meeting' then 1 else 0 end) meeting
from your_table_name
group by date_added, owner;

Find demo here

Upvotes: 2

Related Questions