CODEWITHSUNDEEP
sqloracle-database
Felipe Queiroz
Felipe Queiroz

Reputation: 461

How to get the correct result in the sql query?

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

Answers (2)

Taryn
Taryn

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

Justin Cave
Justin Cave

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

Related Questions