Reputation: 461
I want an sql query to return the following data:
DES_EVENT | DATE_EVENT | INSCRIBED_PARTICIPANTS
And my Tables are (other columns are hidden)
**EVENT**
ID
DES_EVENT
DATE_EVENT
**EVENT_ACCESS**
NAME
EVENT_ID
**PARTICIPANT**
EVENT_ID
NOTE: The table participant registers every event in which a participant is enrolled.
I want to know what events a given user organized and how many participants were enrolled.
I've tried to do the following query:
SELECT
e.des_event,
e.date
FROM
event e,
event_access ea
WHERE
ea.name = ?
AND
ea.event_id = e.id
with this query I can not get the number of participants. So I tried to do the following query:
SELECT
e.des_event,
e.date
FROM
event e,
event_access ea,
participant p
WHERE
ea.name = ?
AND
ea.event_id = e.id
AND
p.event_id = ea.event_id
and the rows of each event are repeated for each participant enrolled on this event, but I want to get this numbers of replicates in another column in the result of the sql like I put on the top of the question.
If I use the count() function the oracle returns an error
(ORA-00937: not a single-group group function)
How can I get the correct results?
Upvotes: 0
Views: 110
Reputation: 247880
It sounds like you want this:
select e.des_event,
e.date_event,
p.NumberOfParticipants
from event e
inner join event_access ea
on e.id = ea.event_id
inner join
(
select count(*) NumberOfParticipants, event_id
from participant
group by event_id
) p
on ea.event_id = p.event_id
where ea.name = ?
Upvotes: 1
Reputation: 231881
If I understand you, you want
SELECT e.des_event,
e.date,
count(*) num_participants
FROM event e,
event_access ea,
participant p
WHERE ea.name = ?
AND ea.event_id = e.id
AND p.event_id = ea.event_id
GROUP BY e.ddes_event, e.date
If that is not what you want, can you post a bit of sample data and post the results that you are looking for? Describing the results like you've done is great but some concrete test cases may help clarify things.
Upvotes: 1