using count on when creating a view

Question

im have a view that need to include the count of members volunteering for an event. I'm getting an error 'not a single-group group functions'. Any idea how to resolve this?

CREATE VIEW atbyrd.events__view AS
SELECT e.name, e."DATE", b."LIMIT",b.allocated_amount, COUNT(em.member_id), e.comments
FROM events e INNER JOIN budgets b ON b.event_id = e.id
INNER JOIN event_members em ON em.event_id  = e.id;

juergen d · Accepted Answer · 2012-11-26 21:20:30Z

1

SELECT e.name, 
       e."DATE", 
       b."LIMIT",
       b.allocated_amount, 
       (select COUNT(member_id) from event_members) as mem_count, 
       e.comments
FROM events e
INNER JOIN budgets b ON b.event_id = e.id
INNER JOIN event_members em ON em.event_id  = e.id;

answered Nov 26, 2012 at 21:20

juergen d

205k40 gold badges303 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Antarr Byrd Over a year ago

This counts all members not just those for a given event

Antarr Byrd Over a year ago

I modified your answer to filter. Thanks for help

Ben · Accepted Answer · 2012-11-26 21:26:31Z

1

You could use the analytic function count()

SELECT e.name
     , e."DATE"
     , b."LIMIT"
     , b.allocated_amount
     , COUNT(em.member_id) over ( partition by em.event_id )
     , e.comments
  FROM events e 
 INNER JOIN budgets b 
    ON b.event_id = e.id
 INNER JOIN event_members em 
    ON em.event_id  = e.id;

Simply put this counts the number of members per event_id but as it's not an aggregate function no GROUP BY is required. You receive the same value per event_id.

answered Nov 26, 2012 at 21:26

Ben

53.1k36 gold badges133 silver badges156 bronze badges

Collectives™ on Stack Overflow

using count on when creating a view

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related