CODEWITHSUNDEEP
sqlsql-server
Arman Safikhani
Arman Safikhani

Reputation: 945

Finding the decade with largest records, SQL Server

I have the following db diagram :

Database Diagram for Movies

I want to find the decade (for example 1990 to 2000) that has the most number of movies. Actually it only deals with "Movies" table.

Any idea on how to do that?

Upvotes: 3

Views: 8349

Answers (6)

Willey Hute
Willey Hute

Reputation: 1088

In my case, I am having year as a string column. To get the movies grouped by decades,

SELECT DISTINCT NUMRANGE(
CAST(FLOOR(CAST(year AS INT)/ 10) * 10 AS INT),
CAST((FLOOR(CAST(year AS INT)/ 10) * 10) + 9 AS INT)
) AS "decades", COUNT(*) AS "movie_count"
FROM movies
WHERE year IS NOT NULL AND year != ''
GROUP BY decades
ORDER BY movie_count DESC;

This gives the number of movies in that decade. Hope this one helps someone...

Upvotes: 0

A.E.S
A.E.S

Reputation: 1

Despite being an old question I found this solution via trying

    DATE_PART('decade',(year::date)) AS decade,
    DATE_TRUNC('decade',(year::date)) AS decade_truncated,

also works for

    DATE_PART('century',(year::date)) AS decade,
    DATE_TRUNC('century',(year::date)) AS decade_truncated,

Upvotes: 0

UpwardD
UpwardD

Reputation: 767

   SELECT floor(Year(getdate())/10)*10
        , floor(year('5/11/2004')/10)*10
        , floor(Year('7/23/1689')/10)*10
        , floor(Year('7/09/1989')/10)*10

Upvotes: 0

lheria
lheria

Reputation: 601

An alternative to the string approach is to use integer division to get the decade:

SELECT [Year]/10*10 as [Decade]
     , COUNT(*) as [CountMovies]
FROM Movies
GROUP BY [Year]/10*10
ORDER BY [CountMovies] DESC

This returns all, ordered by the decade(s) with the most movies. You could add a TOP (1) to only get the top, but then you'd need to consider tiebreaker scenarios to ensure you get deterministic results.

Upvotes: 3

Donal
Donal

Reputation: 32713

You can use the LEFT function in SQL Server to get the decade from the year. The decade is the first 3 digits of the year. You can group by the decade and then count the number of movies. If you sort, or order, the results by the number of movies - the decade with the largest number of movies will be at the top. For example:

select
count(id) as number_of_movies,
left(cast([year] as varchar(4)), 3) + '0s' as decade
from movies
group by left(cast([year] as varchar(4)), 3)
order by number_of_movies desc

Upvotes: 5

JBrooks
JBrooks

Reputation: 10013

select substring(cast([year] as varchar), 1, 3) as Decade,
Count(1) [Count]
from Movies
group by substring(cast([year] as varchar), 1, 3)
order by 2 desc

Upvotes: 0

Related Questions