Is there a way to count auto-incrementally?-CodePudding

The question is:

Get the 5 podcasts with the most views, each listen is counted if timeDuration > 29s (only taking active songs). Each 29s will count as 1 TIMES so it will count base on PodcastID. I think im using JOIN wrong condition so it can not get what I want.

Here is what I have tried:

SELECT TOP (5) E.ID,E.Name,R.PodcastID,
COUNT (R.PodcastID) TIMES
FROM (REPORT R JOIN EPISODE E ON R.PodcastID = E.PodcastID) JOIN 
PODCAST P ON P.ID = E.ID 
WHERE R.TimeDuration > 29 AND E.Status = 1
GROUP BY E.ID, E.Name,R.PodcastID
ORDER BY TIMES;

Here is the 3 Tables that I have created:

--But the output, at the column TIMES no matter I do they only count as 1, although I have set the variable above that: 58s, 116s ( mean 2, 4 times)

Here is the result I want but get top5 only, in the picture is 6 results although I Select top(5)..

Sorry im new with StackOverFlow so the way i show my question and data sample maybe a little bit hard to understand. Please forgive me.

CodePudding user response：

If I understand correctly, then a report row means, someone listened to music in the podcast. The row contains the duration in seconds which you want to regard as a number of blocks of 29 seconds. So, divide the durations by 29 and work with this.

with listened as
(
  select
    id as id_podcast,
    sum(timeduration / 29) as times,
  from report1
  where timeduration > 29
  group by id
)
select top(5) with ties
  p.*, listened.times
from podcast p
join listened on listened.id_podcast = p.id
where  p.status = 1
order by listened.times desc;

It looks strange that you join podcast and report on their IDs. The ID of a report should identify a report not a podcast. Well, I just used it as you did, but created an alias name to make this readable. If this was a mere mistake on your side, then it will be easy to correct this.

If timedureation is an integer, SQL Server will apply integer division by the way. If you want to work with fractions, divide by 29.0 instead of by 29. This may result in much higher time totals per podcast.

I am using the WITH TIES clause, which is common when looking for the top n rows. If you want to restrict this to five result rows where you may have to pick rows from the complete top list arbitrarily, then remove that clause.