sql query: finding average of.. after count

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

sql query: finding average of.. after count

Author

Topic

vik
Starting Member

8 Posts

Posted - 2010-03-13 : 17:22:21

PLEASE HELP ME ANSWER THIS! I'M STRUGGLING!

What is the average number of performances of each event that is taking place during April 2010?

TABLE event:
EVENT_NO
EVENT_DESCRIPTION
START_DATE
END_DATE
AVERAGE_LENGTH

TABLE: performance:
PERFORMANCE_NO
EVENT_NO
PERF_DATE
START_TIME
ACTUAL_END_TIME
UNSOLD_SEATS

I WOULD REALLY APPRECIATE ANY HELP IN THIS

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-13 : 17:27:18

Please show us some sample data and the expected output using that sample data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

vik
Starting Member

8 Posts

Posted - 2010-03-13 : 17:37:27

part of my sql script:

CREATE TABLE EVENT
(EVENT_NO CHAR(5) NOT NULL,
EVENT_DESCRIPTION CHAR(35) NOT NULL,
START_DATE DATE,
END_DATE DATE,
AVERAGE_LENGTH NUMBER(2),
CONSTRAINT EVENT_PRIMARY_KEY PRIMARY KEY (EVENT_NO));

INSERT INTO EVENT VALUES ('E0001', 'PETER PAN', '19-APR-10', '29-APR-10', 90);
INSERT INTO EVENT VALUES ('E0003', 'CHRIS ROCK', '25-OCT-10', '25-NOV-10', 60);
INSERT INTO EVENT VALUES ('E0005', 'LION KING', '14-JAN-10', '30-MAR-10', 90);
INSERT INTO EVENT VALUES ('E0007', 'DRACULA', '10-APR-10', '30-APR-10', 70);
INSERT INTO EVENT VALUES ('E0009', 'SNOW WHITE', '01-DEC-10', '30-DEC-10', 90);
INSERT INTO EVENT VALUES ('E0013', 'SLEEPING BEAUTY', '12-MAY-10', '12-AUG-10', 90);
INSERT INTO EVENT VALUES ('E0020', 'STOMP', '16-APR-10', '18-APR-10', 70);
INSERT INTO EVENT VALUES ('E0025', 'PINOCCHIO', '26-SEP-10', '26-OCT-10', 90);
INSERT INTO EVENT VALUES ('E0029', 'PIRATES', '03-JAN-10', '03-MAY-10', 50);
INSERT INTO EVENT VALUES ('E0030', 'BAMBY', '15-DEC-10', '30-DEC-10', 90);

CREATE TABLE PERFORMANCE
(PERFORMANCE_NO CHAR(5) NOT NULL,
EVENT_NO CHAR(5),
PERF_DATE DATE,
START_TIME CHAR(5),
ACTUAL_END_TIME CHAR(5),

UNSOLD_SEATS NUMBER(3) NOT NULL,
CONSTRAINT PERFORMANCE_FOREIGN_KEY FOREIGN KEY (EVENT_NO) REFERENCES EVENT(EVENT_NO),
CONSTRAINT PERFORMANCE_PRIMARY_KEY PRIMARY KEY (PERFORMANCE_NO));

INSERT INTO PERFORMANCE VALUES ('P0003', 'E0001', '19-APR-10', '14.00', '15.30', 000);
INSERT INTO PERFORMANCE VALUES ('P0004', 'E0001', '29-APR-10', '14.00', '15.30', 000);
INSERT INTO PERFORMANCE VALUES ('P0005', 'E0003', '25-OCT-10', '21.00', '22.00', 002);
INSERT INTO PERFORMANCE VALUES ('P0006', 'E0003', '25-NOV-10', '21.00', '22.00', 003);
INSERT INTO PERFORMANCE VALUES ('P0007', 'E0005', '14-JAN-10', '13.30', '15.00', 000);
INSERT INTO PERFORMANCE VALUES ('P0008', 'E0005', '14-FEB-10', '13.30', '15.00', 002);
INSERT INTO PERFORMANCE VALUES ('P0009', 'E0005', '30-MAR-10', '13.30', '15.00', 000);
INSERT INTO PERFORMANCE VALUES ('P0010', 'E0007', '10-APR-10', '19.30', '20.40', 004);
INSERT INTO PERFORMANCE VALUES ('P0011', 'E0007', '30-APR-10', '19.30', '20.40', 006);
INSERT INTO PERFORMANCE VALUES ('P0012', 'E0009', '01-DEC-10', '19.30', '21.00', 010);
INSERT INTO PERFORMANCE VALUES ('P0013', 'E0009', '10-DEC-10', '19.30', '21.00', 014);
INSERT INTO PERFORMANCE VALUES ('P0014', 'E0009', '15-DEC-10', '19.30', '21.00', 010);
INSERT INTO PERFORMANCE VALUES ('P0015', 'E0009', '30-DEC-10', '19.30', '21.00', 010);
INSERT INTO PERFORMANCE VALUES ('P0016', 'E0013', '12-MAY-10', '12.00', '13.30', 080);
INSERT INTO PERFORMANCE VALUES ('P0017', 'E0013', '12-AUG-10', '12.00', '13.30', 030);
INSERT INTO PERFORMANCE VALUES ('P0018', 'E0020', '16-APR-10', '13.00', '14.10', 009);
INSERT INTO PERFORMANCE VALUES ('P0020', 'E0020', '17-APR-10', '14.30', '15.40', 008);
INSERT INTO PERFORMANCE VALUES ('P0021', 'E0029', '03-JAN-10', '21.00', '21.50', 001);
INSERT INTO PERFORMANCE VALUES ('P0022', 'E0029', '03-FEB-10', '21.00', '21.50', 011);
INSERT INTO PERFORMANCE VALUES ('P0023', 'E0029', '03-MAR-10', '21.00', '21.50', 004);
INSERT INTO PERFORMANCE VALUES ('P0024', 'E0029', '03-APR-10', '21.00', '21.50', 001);
INSERT INTO PERFORMANCE VALUES ('P0025', 'E0029', '03-MAY-10', '21.00', '21.50', 001);
INSERT INTO PERFORMANCE VALUES ('P0026', 'E0030', '15-DEC-10', '13.30', '15.00', 023);
INSERT INTO PERFORMANCE VALUES ('P0027', 'E0030', '30-DEC-10', '13.30', '15.00', 078);

i'm expecting to be shown the average 'performances' of each 'event'. suggestions?

vik
Starting Member

8 Posts

Posted - 2010-03-13 : 17:52:49

output similar to:

description average performances
PETER PAN 2

vik
Starting Member

8 Posts

Posted - 2010-03-13 : 18:32:31

please i am in desperate need of help on this!

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-03-13 : 19:39:31

Still not real clear on what you want, but your "times" are not stored in time...so that makes it unclear.

Using some liberal assumptions, and modifying your sample data to provide a meaningful average for the length of the performance...the below works. I am sure a pivot operator could be used as well. Was the problem that you couldn't average the time of the performances since they aren't stored as time? You can store them as string, but have to cast them as datetime before using them as time...anyhoo

Maybe you could be more clear on the desired result?


drop table performance
drop table [event]


CREATE TABLE [EVENT]
([EVENT_NO] CHAR(5) NOT NULL,
[EVENT_DESCRIPTION] CHAR(35) NOT NULL,
START_DATE DATEtime not null,
END_DATE DATEtime not null,
AVERAGE_LENGTH int not null, 
CONSTRAINT EVENT_PRIMARY_KEY PRIMARY KEY (EVENT_NO));

INSERT INTO [EVENT] VALUES ('E0001', 'PETER PAN', '19-APR-10', '29-APR-10', 90);
INSERT INTO [EVENT] VALUES ('E0003', 'CHRIS ROCK', '25-OCT-10', '25-NOV-10', 60);
INSERT INTO [EVENT] VALUES ('E0005', 'LION KING', '14-JAN-10', '30-MAR-10', 90);
INSERT INTO [EVENT] VALUES ('E0007', 'DRACULA', '10-APR-10', '30-APR-10', 70);
INSERT INTO [EVENT] VALUES ('E0009', 'SNOW WHITE', '01-DEC-10', '30-DEC-10', 90);
INSERT INTO [EVENT] VALUES ('E0013', 'SLEEPING BEAUTY', '12-MAY-10', '12-AUG-10', 90);
INSERT INTO [EVENT] VALUES ('E0020', 'STOMP', '16-APR-10', '18-APR-10', 70);
INSERT INTO [EVENT] VALUES ('E0025', 'PINOCCHIO', '26-SEP-10', '26-OCT-10', 90);
INSERT INTO [EVENT] VALUES ('E0029', 'PIRATES', '03-JAN-10', '03-MAY-10', 50);
INSERT INTO [EVENT] VALUES ('E0030', 'BAMBY', '15-DEC-10', '30-DEC-10', 90);



CREATE TABLE PERFORMANCE
(PERFORMANCE_NO CHAR(5) NOT NULL,
[EVENT_NO]CHAR(5)not null,
PERF_DATE smallDATEtime not null,
START_TIME DATEtime not null,
ACTUAL_END_TIME DATEtime not null,
UNSOLD_SEATS int  NOT NULL,
CONSTRAINT PERFORMANCE_FOREIGN_KEY FOREIGN KEY ([EVENT_NO]) REFERENCES [EVENT](EVENT_NO),
CONSTRAINT PERFORMANCE_PRIMARY_KEY PRIMARY KEY (PERFORMANCE_NO));

INSERT INTO PERFORMANCE VALUES ('P0003', 'E0001', '19-APR-10', '14:00', '15:30', 000);
INSERT INTO PERFORMANCE VALUES ('P0004', 'E0001', '29-APR-10', '14:00', '15:40', 000);
INSERT INTO PERFORMANCE VALUES ('P0005', 'E0003', '25-OCT-10', '21:00', '22:40', 002);
INSERT INTO PERFORMANCE VALUES ('P0006', 'E0003', '25-NOV-10', '21:00', '22:00', 003);
INSERT INTO PERFORMANCE VALUES ('P0007', 'E0005', '14-JAN-10', '13:30', '15:40', 000);
INSERT INTO PERFORMANCE VALUES ('P0008', 'E0005', '14-FEB-10', '13:30', '15:00', 002);
INSERT INTO PERFORMANCE VALUES ('P0009', 'E0005', '30-MAR-10', '13:30', '15:40', 000);
INSERT INTO PERFORMANCE VALUES ('P0010', 'E0007', '10-APR-10', '19:30', '20:40', 004);
INSERT INTO PERFORMANCE VALUES ('P0011', 'E0007', '30-APR-10', '19:30', '20:40', 006);
INSERT INTO PERFORMANCE VALUES ('P0012', 'E0009', '01-DEC-10', '19:30', '21:20', 010);
INSERT INTO PERFORMANCE VALUES ('P0013', 'E0009', '10-DEC-10', '19:30', '21:10', 014);
INSERT INTO PERFORMANCE VALUES ('P0014', 'E0009', '15-DEC-10', '19:30', '21:10', 010);
INSERT INTO PERFORMANCE VALUES ('P0015', 'E0009', '30-DEC-10', '19:30', '21:40', 010);
INSERT INTO PERFORMANCE VALUES ('P0016', 'E0013', '12-MAY-10', '12:00', '13:20', 080);
INSERT INTO PERFORMANCE VALUES ('P0017', 'E0013', '12-AUG-10', '12:00', '13:30', 030);
INSERT INTO PERFORMANCE VALUES ('P0018', 'E0020', '16-APR-10', '13:00', '14:10', 009);
INSERT INTO PERFORMANCE VALUES ('P0020', 'E0020', '17-APR-10', '14:30', '15:40', 008);
INSERT INTO PERFORMANCE VALUES ('P0021', 'E0029', '03-JAN-10', '21:00', '21:50', 001);
INSERT INTO PERFORMANCE VALUES ('P0022', 'E0029', '03-FEB-10', '21:00', '21:50', 011);
INSERT INTO PERFORMANCE VALUES ('P0023', 'E0029', '03-MAR-10', '21:00', '21:50', 004);
INSERT INTO PERFORMANCE VALUES ('P0024', 'E0029', '03-APR-10', '21:00', '21:50', 001);
INSERT INTO PERFORMANCE VALUES ('P0025', 'E0029', '03-MAY-10', '21:00', '21:50', 001);
INSERT INTO PERFORMANCE VALUES ('P0026', 'E0030', '15-DEC-10', '13:30', '15:00', 023);
INSERT INTO PERFORMANCE VALUES ('P0027', 'E0030', '30-DEC-10', '13:30', '15:00', 078);
Select [EVENT].EVENT_DESCRIPTION
	   ,COUNT(*) as NUMEVENTS
	   ,AVG(datediff(mi,start_time,actual_end_time)) as AVGTIME
	   ,AVG(UNSOLD_SEATS) as AVGUNSOLDSEATS
FROM [EVENT] inner join [PERFORMANCE] on [EVENT].EVENT_NO = PERFORMANCE.EVENT_NO
GROUP BY [EVENT].EVENT_DESCRIPTION

/*
EVENT_DESCRIPTION	NUMEVENTS	AVGTIME	AVGUNSOLDSEATS
BAMBY                              	2	90	50
CHRIS ROCK                         	2	80	2
DRACULA                            	2	70	5
LION KING                          	3	116	0
PETER PAN                          	2	95	0
PIRATES                            	5	50	3
SLEEPING BEAUTY                    	2	85	55
SNOW WHITE                         	4	110	11
STOMP                              	2	70	8
*/

This will summarize using teh above as a subquery:



Select  SUM(NUMEVENTS) as TOTEVENTS
		,COUNT(DISTINCT EVENT_DESCRIPTION) as UNIQUE_EVENTS
		,AVG(convert(numeric(5,2),NUMEVENTS)) as AvgPerformancesPerEvent
		,AVG(AVGTIME) as AVGTimes
		,AVG(AVGUNSOLDSEATS) as AVGUnsold
FROM ( Select [EVENT].EVENT_DESCRIPTION
	   ,COUNT(*) as NUMEVENTS
	   ,AVG(datediff(mi,start_time,actual_end_time)) as AVGTIME
	   ,AVG(UNSOLD_SEATS) as AVGUNSOLDSEATS
		FROM [EVENT] inner join [PERFORMANCE] on [EVENT].EVENT_NO = PERFORMANCE.EVENT_NO
		GROUP BY [EVENT].EVENT_DESCRIPTION ) events

/*
TOTEVENTS	UNIQUE_EVENTS	AvgPerformancesPerEvent	AVGTimes	AVGUnsold
24	9	2.666666	85	14

Not awesome or anything..but is this what you are trying to do?

Poor planning on your part does not constitute an emergency on my part.

vik
Starting Member

8 Posts

Posted - 2010-03-14 : 08:58:53

i was actually trying to show the average number of performances, of each event. so i think i 1st need to count the number of performances for each event, then take an average of the resulting numbers. any ideas how to do this?

(by the way i appreciate your help so far)

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-03-14 : 10:59:39

I am pretty sure that is what I just showed you.

your new post says "i need to answer an sql query as i am having difficulty on it. the query is:

Produce a report that shows for each event with a start_date of April 1st 2010 or later: the event description, each performance date, and the number of seats sold for that performance."

You don't have total seats available in the data shown, but total seats sold , would simply be the TotalSeatsAvailable minus the Unsold Seats figure.

For an average, you have to have a denominator for each event. Peter Pan was twice in april. Exactly what average would need after that? average per day? average per month (well, for 1 month of april--it would still be 2).

Poor planning on your part does not constitute an emergency on my part.

vik
Starting Member

8 Posts

Posted - 2010-03-14 : 11:24:43

well, for the new query i posted, it says to produce a report, does this mean i need to "create view"?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-03-14 : 12:10:05

Only you can determine what the meaning of the request is. A report is usually a deliverable, a view is something you could query off of..but the report could be a view. Can't help you produce what you need, if you don't even know..

Poor planning on your part does not constitute an emergency on my part.

vik
Starting Member

8 Posts

Posted - 2010-03-14 : 12:21:16

ok, i'll try and give it another shot at college tomorrow. thanks for your help :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources