Error in count/group by for query inside another query-CodePudding

I'm trying to extract the total number of ratings published in a specific year from this database:

This is the code I entered to extract this number:

SELECT Count(*) NrOfRatings
FROM (SELECT timestamp, FROM_UNIXTIME(timestamp) as datetime, 
CAST(FROM_UNIXTIME(timestamp) as date) AS date_value 
FROM ratings
WHERE year(CAST(FROM_UNIXTIME(timestamp) as date) )=2000) NrOfRatings
GROUP BY NrOfRatings;

I think the problem comes from the function GROUP BY linked to the count function. I tried grouping by movieId, timestamp and others but nothing seems to work.

CodePudding user response：

What you want is count of number of ratings for same year occurring in multiple rows to be grouped and get the count of rating.

If you want it to be just for one specific year than you can do as below

select count(1) from Ratings where year(CAST(FROM_UNIXTIME(timestamp) as date))=2000

But if you want to know count of ratings for every set of years than you need to partition your data based on year and than just count over it.

SELECT *,year(CAST(FROM_UNIXTIME(timestamp) as date)) as yearForTimeStamp,
COUNT(*) Over(Partition by year(CAST(FROM_UNIXTIME(timestamp) as date))) as
NoOfRating 
FROM Ratings

Running query with example

CodePudding user response：

Grouping on the count function wasn't needed.
One would group by what's not in an aggregation function like COUNT, MIN, MAX, AVG, ...

For example, grouping by the calculated year

select 
 year(from_unixtime(`timestamp`)) as `year`, 
 count(*) as NrOfRatings
from ratings
where year(from_unixtime(`timestamp`)) = 2000
group by `year`