sql - 查询以计算演出收入占所有演出总收入的比例

Question

我希望对某个演出的票数+该演出的总收入与所有演出的总收入进行比较。我已经成功计算出预订的票数，但是完整的查询无法正常工作，并给了我错误。

示例结果如下:

`tickets booked = 9
 total revenue of the performance = xxx$
 total revenue of all the performances = xxx$

我正在使用的查询是这样的:

 SET @performancerevenue = '50 Shades of Grey';
 select distinct P.perName as Performance, 
 (select COUNT(ticketBooked) from bookings as B 
 inner join performances as P on 
 B.perID = p.perID where perName = @performancerevenue) as
 [Number of Ticket Booked ], 
 CAST(AVG((T.ticketPrice*left(B.totalCost,1))) over() as decimal(6,2))    as[Total Revenue from the performance (£)], 
 CAST(AVG(convert(decimal(5,2),(left(PY.totalAmount,1)))) over() as   decimal(5,2)) as [Total revenue]
 from performances as P
 inner join tickets as T
 on T.perID = P.perID
 inner join bookings as B
 on T.bookingID = B.bookingID
 inner join payments as PY
 where P.perName = @performancerevenue

表格和样本数据如下:

 CREATE TABLE bookings( 
 bookingID int IDENTITY (1,1) NOT NULL,  
 customerID int ,
 clerkID varchar (20) ,
 perID int,
 ticketBooked int, 
 bookingDate Date,   
 bookingTime time,
 bookingType varchar(20),
 totalCost decimal(5,2),
 PRIMARY KEY (bookingID),
 FOREIGN KEY (customerID) REFERENCES customers,
 FOREIGN Key (clerkID) REFERENCES clerks,);

 insert into bookings values (16, 'clerk04', 1, 2, '2015-02-24', '12:00',  'Normal', '20');
 insert into bookings values (2, 'clerk02', 20,3, '2015-02-25', '15:00', 'Advance', '34');
 insert into bookings values (10, 'clerk04', 3,5, '2015-02-17', '18:00', 'Advance', '75');
 insert into bookings values (5, 'clerk01', 19,2, '2015-01-24', '21:00', 'Advance', '30');
 insert into bookings values (13, 'clerk01', 4,1, '2015-02-05', '12:00', 'Normal', '10');
 insert into bookings values (20, 'clerk04', 5,1,'2015-01-26', '15:00', 'Normal', '12');
 insert into bookings values (4, 'clerk03', 6, 3,'2015-02-09', '18:00', 'Advance', '30');
 insert into bookings values (1, 'clerk03', 2, 1,'2015-01-03', '21:00', 'Advance', '15');
 insert into bookings values (17, 'clerk04', 3, 1,'2015-02-09', '12:00', 'Normal', '12');


 CREATE TABLE performances( 
 perID int IDENTITY (1,1)NOT NULL,  
 perName varchar (50),
 perTime Time,
 perDate Date, 
 perType varchar (20),
 duration varchar (10),
 screenNo int,
 rating varchar (10),
 location varchar (10),
 PRIMARY KEY (perID),);
 insert into performances values ('50 Shades of Grey', '12:00', '2015-03-08', 'Movie', '2hrs', '3', 'PG', 'Chelmsford');
 insert into performances values ('50 Shades of Grey', '15:00', '2015-02-20', 'Movie', '2hrs', '2', '18', 'Chelmsford');
 insert into performances values ('50 Shades of Grey', '18:00', '2015-02-26', 'Movie', '2hrs', '3', '18', 'Chelmsford');
 insert into performances values ('50 Shades of Grey', '21:00', '2015-03-28', 'Movie', '2hrs', '2', 'PG', 'Chelmsford');
 insert into performances values ('Paddington', '12:00', '2015-03-26', 'Movie', '2:30hrs', '2', '18', 'Chelmsford');
 insert into performances values ('Paddington', '15:00', '2015-03-03', 'Movie', '2:30hrs', '3', 'PG', 'Chelmsford');
 insert into performances values ('Paddington', '18:00', '2015-02-02', 'Movie', '2:3hrs', '3', 'PG', 'Chelmsford');
 insert into performances values ('Paddington', '21:00', '2015-03-31', 'Movie', '2:30hrs', '1', '18', 'Chelmsford');
 insert into performances values ('Shakespeare', '12:00', '2015-02-25', 'Movie', '3hrs', '2', '18', 'Chelmsford');
 insert into performances values ('Shakespeare', '15:00', '2015-02-06', 'Movie', '3hrs', '3', 'PG', 'Chelmsford');
 insert into performances values ('Shakespeare', '18:00', '2015-02-13', 'Movie', '3hrs', '3', '18', 'Chelmsford');
 insert into performances values ('Shakespeare', '21:00', '2015-01-09', 'Movie', '3hrs', '3', 'PG', 'Chelmsford');
 insert into performances values ('Jupiter Ascending', '12:00', '2015-01-11', 'Movie', '2:3hrs', '1', '18', 'Chelmsford');
 insert into performances values ('Jupiter Ascending', '15:00', '2015-01-19', 'Movie', '2:30hrs', '2', '18', 'Chelmsford');
 insert into performances values ('Jupiter Ascending', '18:00', '2015-02-06', 'Movie', '2:3hrs', '3', 'PG', 'Chelmsford');
 insert into performances values ('Jupiter Ascending', '21:00', '2015-01-02', 'Movie', '2:3hrs', '2', 'PG', 'Chelmsford');
 insert into performances values ('50 Shades of Grey', '12:00', '2015-01-06', 'Movie', '2hrs', '1', '18', 'Chelmsford');
 insert into performances values ('50 Shades of Grey', '15:00', '2015-03-28', 'Movie', '2hrs', '3', 'PG', 'Chelmsford');
 insert into performances values ('50 Shades of Grey', '18:00', '2015-02-09', 'Movie', '2hrs', '2', 'PG', 'Chelmsford');
 insert into performances values ('50 Shades of Grey', '21:00', '2015-02-06', 'Movie', '2hrs', '2', '18', 'Chelmsford');

 CREATE TABLE payments( 
 paymentID int IDENTITY (1,1) not null,
 bookingID int, 
 totalAmount decimal (5,2),
 paymentType varchar (20), 
 paymentDate Date, 
 PRIMARY KEY (paymentID),
 FOREIGN KEY (bookingID) REFERENCES bookings,);
 insert into payments values (1, 20, 'Cash', '2015-01-20');
 insert into payments values (10, 20, 'Cash', '2015-01-17');
 insert into payments values (12, 12, 'Credit/Debit Card', '2015-03-31');
 insert into payments values (5, 10, 'Cash', '2015-01-08');
 insert into payments values (18, 12, 'Cash', '2015-03-22');
 insert into payments values (3, 75, 'Credit/Debit Card', '2015-02-21');
 insert into payments values (2, 34, 'Credit/Debit Card', '2015-03-26');
 insert into payments values (9, 12, 'Cash', '2015-03-26');
 insert into payments values (13, 20, 'Credit/Debit Card', '2015-01-23');
 insert into payments values (16, 20, 'Credit/Debit Card', '2015-03-08');
 insert into payments values (11, 45, 'Credit/Debit Card', '2015-02-21');
 insert into payments values (4, 30, 'Credit/Debit Card', '2015-02-08');
 insert into payments values (7, 30, 'Cash', '2015-01-20');
 insert into payments values (20, 30, 'Cash', '2015-02-26');
 insert into payments values (14, 20, 'Credit/Debit Card', '2015-03-17');
 insert into payments values (19, 45, 'Cash', '2015-01-10');
 insert into payments values (6, 12, 'Credit/Debit Card', '2015-02-02');
 insert into payments values (20, 30, 'Cash', '2015-02-01');
 insert into payments values (17, 34, 'Credit/Debit Card', '2015-03-27');
 insert into payments values (15, 15, 'Credit/Debit Card', '2015-02-14');  

我一直在努力使结果在最近4个小时内有效，但这给了我错误，我将非常感谢您的帮助:(

谢谢

Answer 1

我认为您可以通过以下方式获得所需的结果:

SELECT DISTINCT 
 P.perName as [Perf], 
 SUM(B.ticketBooked) OVER(PARTITION BY P.perName) as [BookedTickets],
 SUM(B.totalCost) OVER(PARTITION BY P.perName) as [PerfRevenue],
 SUM(B.totalCost) OVER() as [TotalRevenue]
 from bookings as B 
 inner join performances as P on B.perID = p.perID

该查询结果如下:

------------------------------------------------------------
Perf                BookedTickets   PerfRevenue TotalRevenue
------------------+---------------+------------+------------
50 Shades of Grey   15              196.00      238.00
Paddington           4              42.00       238.00

您可以为该表添加任何外部查询，以进行其他过滤和计算。