create or replace view member_counts as select members.id as id, members.id as member_id, ( select COUNT(*) from postings where postings.member_id = members.id and postings.type = 'TradeNote' ) as trade_note_count, ( select COUNT(*) from postings where postings.member_id = members.id and postings.type = 'Blog' ) as blog_count, ( 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;