I need a help for solving SQL query

Question

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 13-Mar-23 21:34pm

An@mik@

Updated 14-Mar-23 1:04am

OriginalGriff

v2

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Accepted Answer · 2023-03-14T01:04:00

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)