Database SQL - Query to find top 5 values and total generated by each value

Question

0.00/5 (No votes)

See more:

I need an SQL query to list the top 5 most frequently rented DVD's, sorted in descending
order. For each record i need to show: DVD title, the rented frequency and the
total rent income generated by each DVD title.

My table:
DVD

My attributes:
DVD-Title,
DVD-sales (Number of sales of the single DVD title in that record),
DVD-Total (Total of sales (£) of the single DVD title in that record

I have got this myself:

SELECT DVD-title, DVD-sales
FROM DVD
WHERE
(
DVD-sales IN
(
SELECT TOP (5) DVD-sales
FROM table as DVD
GROUP BY DVD-sales
ORDER BY DVD-sales DESC
)
)

(is "table as" a key word? or should i put my table name there?)

Not sure if the above code is right or not, and i don't know how to do the total rent income generated for each DVD title

Please include a SQL code (SQL server code is fine) solution in your answers, thank you!

Posted 5-Jan-15 5:40am

Newbie12345

Updated 5-Jan-15 6:23am

v3

Add a Solution

Comments

DiponRoy 5-Jan-15 12:05pm

you should provide the related tables struct.

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)

jaket-cp · Answer 1 · 2015-01-05T06:21:00

Unless I am missing something, you should be able to do a straight forward top 5 select and order by the required column.

SQL

--test data setup
declare @DVD table (id int identity(1,1),[DVD-title] varchar(100),[DVD-sales] int, [DVD-total] decimal(5,2));
insert into @DVD
select 'titleI', 9, 27
union all select 'titleJ', 6, 18
union all select 'titleG', 3, 9
union all select 'titleB', 2, 6
union all select 'titleE', 2, 6
union all select 'titleC', 10, 30
union all select 'titleD', 4, 12
union all select 'titleA', 5, 15
union all select 'titleF', 9, 27
union all select 'titleH', 12, 36

select top (5)
	[DVD-title],
	[DVD-sales],
	[DVD-total]
from @DVD
order by [DVD-sales] desc
;