December 3, 2010 at 1:25 pm
I'm trying to use the MAX eventID from table attendance so that I can take the corresponding eventID That has the max and compare that to the eventID of table event so I can have the event name with the highest number of attendance.
SELECT e.name, c.eventid, c.cnt
FROM event e, (SELECT eventid, cnt = count(eventid)
FROM attendance
GROUP BY eventid
HAVING cnt >= ALL(SELECT count(eventid)
FROM attendance
GROUP BY eventid))c
WHERE e.eventid = c.eventid;
If anyone has any ideas, please help.
My other issue is that I keep seeing FROM keyword not found where expected.
December 3, 2010 at 1:36 pm
I goofed off with this but the data layout seems a little strange to me, it may just be some internal assumptions I'm making about the data.
Any chance you can provide DDL/sample data like my first link to help us help you out?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 3, 2010 at 1:40 pm
Of course,
I'll show the setup of the tables.
CREATE TABLE Event(
EventID number(6),
Location VARCHAR2(50),
Description VARCHAR2(50),
Cost number(10,2),
EventDate date,
Name VARCHAR2(50),
TotAttendance number(7),
AdmitPrice number(10,2),
StaffEmployeeID number(6),
PlanningHours number(7),
CONSTRAINT Event_pk PRIMARY KEY(EventID),
CONSTRAINT EventStaff_fk FOREIGN KEY(StaffEmployeeID) REFERENCES Staff(EmployeeID));
CREATE TABLE Attendance(
EventID number(6),
StudentCMID number(9),
CONSTRAINT Attendance_pk PRIMARY KEY(EventID, StudentCMID),
CONSTRAINT EventAttendance_fk FOREIGN KEY(EventID) REFERENCES Event(EventID),
CONSTRAINT StudentAttendance_fk FOREIGN KEY(StudentCMID) REFERENCES Student(CMID));
Within the two tables, the data is formatted as shown but Attendance is a large list of eventids and the studentCMID that attended.
IE:
INSERT INTO Attendance values(999123, 000945873);
INSERT INTO Attendance values(999123, 000940894);
INSERT INTO Attendance values(999123, 000942424);
INSERT INTO Attendance values(999123, 000945601);
INSERT INTO Attendance values(999123, 000946458);
etc.
December 3, 2010 at 1:51 pm
Ah, that helped cure my confusion. I apparently just can't read. I was, for some reason, trying to get multiple events out of the structure. :hehe:
What you've got is pretty much the primary method. I use a slightly different variation by equating to a MAX( cnt), but it ends up as the same result. I use that primarily because I'm usually looking for max's within groups.
The other approach to this would be, since you want a single result:
SELECT TOP 1
e.eventID,
e.name,
drv.cnt
FROM
[event] AS e
JOIN
(SELECT eventID, COUNT(*) AS cnt
FROM attendance
GROUP BY eventID
) AS drv
ONe.eventid = drv.eventid
order by
drv.cnt desc
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply