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 end case as 'trade_note_count', case when postings_type = 'Blog' then cpt end case as 'blog_count' from( select count(*) as cpt, postings_type from postings where 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;