CODEWITHSUNDEEP
sqljoinoracle11g
Antarr Byrd
Antarr Byrd

Reputation: 26131

using count on when creating a view

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;

Upvotes: 0

Views: 47

Answers (2)

Ben
Ben

Reputation: 52893

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.

Upvotes: 1

juergen d
juergen d

Reputation: 204854

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;

Upvotes: 1

Related Questions