Top n Query

[ocp&mcsd]

Dear all,
How are you?
Sorry for disturbing you.

I have a table called Cinema with two columns Movie, and Rate.
For example, if I have the following data
Titanic 40
Walking Tall 30
Longest Yard 20
Cars 4
Monster Inc 3
The Hitcher 2
Pathfinder 1

I want a query to show the top 3, and show the rest movies together.

Titanic 40
Walking Tall 30
Longest Yard 20
Others 10

How can I do it?
I tried the below select statement, but It will show only the top 3.
SELECT TOP 3 Cinema.Movie, Cinema.Rate
FROM Cinema;

Thanks in advance

 

[Banana]

You would need to do a UNION query to lump the others as 'Other'. Something like this:

SELECT TOP 3 a,b,c FROM x
UNION 
SELECT SUM(d,e,f) FROM X WHERE NOT IN(SELECT TOP 3 a,b,c FROM x;);

 

[ocp&mcsd]

Thanks
But it did not work.

Kindly, can you fix it??

 

[Rabbie]

If you tell us exactly what you did and what did not work you are more liikely to get an answer to your questions.

 

[ocp&mcsd]

Thank you..
I wrote

SELECT TOP 3 Cinema.Movie, Cinema.Rate
FROM Cinema
Union
Select Sum(Cinema.Rate) from Cinema where Cinema.Movie not in (Select top 3 Cinema.Movie from Cinema);

Kindly, find attached the db.

 

[Rabbie]

Try this

SELECT TOP 3 Cinema.Movie, Cinema.Rate
FROM Cinema
Union
SELECT "Other" as Movie,Sum(Cinema.Rate) AS Rate
FROM Cinema
WHERE (((Cinema.Movie) Not In (Select top 3 Cinema.Movie from Cinema))) Order by Rate Desc;

 

[ocp&mcsd]

Thank you so much my dear...

 

[Rabbie]

Glad to have been of help

 

[Banana]

Just so you know, UNION requires that you have a equal number of columns for both queries, which is probably why it didn't work the first time.

Rabbie, thanks!

 

[ocp&mcsd]

Dear Mr. Rabbie,

How are you?

I need your help.

If I want to order by CinemaID from Theatre table, how can I do it?

Sorry for disturbing my dear.

Kindly find attached copy of the db.

 

[Rabbie]

Not quite sure what you are looking for but this query should display them in the right order.

SELECT CinemaID, CinemaName from Theatres order by CinemaID;

HTH

 

[ocp&mcsd]

I want to do what you have done for me before (See the above replies), but I want to show the same but by Cinema ID.

 

[ocp&mcsd]

I want to do what you have done for me before (See the above replies), but I want to show the same but by Cinema ID.

 

[Rabbie]

Sorry, I am still not clear how you want the data displayed. Can you post an example please

 

[ocp&mcsd]

I want something like:
CinemaID , MovieName , Rate
1 , Titanic , 40
1 , Rocky , 30
1 , Cars , 20
1 , Others , 10
2 , Toy Story , 50
2 , The Godfather , 30
2 , Finding Nemo, 15
2 , Others , 5

Like that....

I need your help my dear

 

[Rabbie]

Thanks for that. I willsee what I can do. it will take a bit of time