create or replace view member_counts as select members.id as id, members.id as member_id, select case when postings_type = 'TradeNote' then cpt as 'trade_note_count' end casecase when postings_type = 'Blog' then cpt as 'blog_count' end casefrom(select count(*) as cpt, postings_type from postingswhere postings.type in ('TradeNote', 'Blog')group by postings.type), ( select count(distinct posting_id) from member_tags, member_taggings where member_tags.member_id = members.id and member_taggings.member_tag_id = member_tags.id ) as tagged_item_count, ( select count(*) from groups_members where member_id = members.id ) as group_count, ( select count(*) from subscriptions where subscribed_id = members.id ) as subscription_count from members;