Click here to Skip to main content
15,884,472 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Consider the two tables

Table Definitions
Table: dbo.Tickets

Cinema	Month	TicketsSold
1	01/31/23	5
1	02/28/23	10
2	01/31/23	15
2	02/28/23	20
3	01/31/23	25
4	01/31/23	30


Table: dbo.Retail

Cinema	Month	RetailSold
1	01/31/23	10
1	02/28/23	15
2	01/31/23	25
3	01/31/23	30
3	02/28/23	35
5	01/31/23	40

Write a SQL query that provides the cinema and month that had the highest tickets sold and what the retail sold was for that associated cinema and month
OUTPUT I want
Cinema	Month	  TicketsSold	RetailSold
4	     1/31/2023	30	      0



This is my question

What I have tried:

I  wrote a query which is giving following solution

with CTE (ID,Month_name , TSOLD)
as 
(
select 
	cinema.Cinema ,
	MONTH_name , 
	TicketsSold from cinema
where TicketsSold =
(select MAX(TicketsSold) from cinema)
)
select ID,Month_name , TSOLD from  CTE
<pre>
I am getting the following output 

ID     Month_Name       TSold 
4      2023-01-31        30


But How do I get Retail Sold count as 0.
I tried union , join but not getting the output.
Posted
Updated 14-Mar-23 1:04am
v2
Comments
Member 15627495 14-Mar-23 4:25am    
for the retail solds, you have 'SUM('column')',
to merge rows you have 'GROUP BY column'
An@mik@ 14-Mar-23 4:33am    
If you see the data Retail column is from retail Table and cinema ID 4 is not there in retail Table
and If I join then no data will show.

so if this is the situation then it should display 0. Can you give me practical query demo so I can Execute.

I used join but not getting desire result.
with CTE (ID,Month_name , TSOLD)
as
(
select
cinema.Cinema ,
MONTH_name ,
TicketsSold from cinema
where TicketsSold =
(select MAX(TicketsSold) from cinema)
)

select
cte.ID,
CTE.Month_name ,
TSOLD ,
RetailSold ,
cinema
from CTE inner join retail
on cte.Month_name = retail.Month_name

Member 15627495 14-Mar-23 5:08am    
as try :

select cinema, month_name, sum(retail) // column needed as answer
from tickets, retail // tables required
where tickets.cinema = retail.cinema // linking the table , as equal 'join'
group by month_name // merging months
having MAX(ticket_sold) // applying one more criteria with 'MAX(column)' use

CHill60 14-Mar-23 7:12am    
there is no month_name column, you can't sum an entire table like that .. sum(retail), why would you group by anything to get a single row and your criteria is missing from MAX(ticket_sold) - all in all nothing here the OP can actually "try"
Member 15627495 14-Mar-23 5:29am    
the 'month_name' column have more than 'months' in.
there are 'month/day/year' in your column.

1 solution

You need to left outer join your CTE results to the Retail table something like this
SQL
select a.*, ISNULL(b.TicketsSold,0) as RetailTickets
from cte a
left outer join retail b on a.Cinema = b.Cinema 
	and (b.[Month] is null or b.[Month] = a.[Month])
Use Left Outer join because there is no cinema 4 in the retail table. Try doing this with the lowest tickets sold to demo that it still works

Incidentally, I would do the cte slightly differently
SQL
with cte as 
(
	select top 1 * 
	from @tickets
	order by TicketsSold desc
)
(I can use the order by in the cte only because I used top 1)
 
Share this answer
 
Comments
Maciej Los 14-Mar-23 11:28am    
5ed!
An@mik@ 20-Mar-23 4:36am    
Thank you it works.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900