CODEWITHSUNDEEP
sqlsql-serverdatabase
Ray_Hack
Ray_Hack

Reputation: 981

SQL Server : count duplicates and filter by date

I have a data structure that looks like the equivalent of this

UserID| Rating | CreatedDate |
------+--------+-------------+
 101  |  10    |  21-02-2018 |
 101  |  10    |  18-02-2018 |
 102  |   6    |  12-01-2018 |
 103  |   3    |  01-02-2018 |
 101  |   8    |  25-03-2018 |
 102  |   2    |  21-02-2017 |
 101  |   1    |  21-08-2017 |

I am trying to retrieve from SQL Server 2017 a count of all the different ratings (1-10) a particular user has given.

I am using this code:

 SELECT Rating, COUNT(Rating) AS Count
 FROM dbo.tableName
 GROUP BY Rating, UserId
 HAVING (COUNT(Rating) >= 1) AND (UserId = '101')
 ORDER BY Count DESC

The issue I have however is that I wish to only run this query for entries created in the last 5 weeks, to filter to the last 5 weeks I would previously use:

(CreatedDateTime > DATEADD(week, - 5, GETDATE())) AND (Recommend IS NOT NULL)

but when mixed with Count, this is no longer working. I get an error along the line of

CreatedDateTime is invalid in the having clause because it is not contained in either aggregate function or group by clause

Could someone help me get the correct syntax?

Upvotes: 1

Views: 124

Answers (2)

EzLo
EzLo

Reputation: 14189

Move your row filters to the WHERE and keep you aggregate filters on the HAVING.

SELECT        
    Rating, 
    COUNT(Rating) AS Count
FROM
    dbo.tableName
WHERE
    UserId = '101' AND
    CreatedDateTime > DATEADD(week, - 5, GETDATE()) AND 
    Recommend IS NOT NULL
GROUP BY
    Rating
HAVING
    COUNT(Rating) >= 1
ORDER BY
     Count DESC

The SQL engine filter filters data with the WHERE conditions, then groups the rows with the GROUP BY columns, and finally applies HAVING conditions to filter the grouped results.

Upvotes: 3

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

I think your criteria should be in the WHERE clause:

SELECT
    Rating,
    COUNT(Rating) AS Count
FROM dbo.tableName
WHERE
    CreatedDateTime > DATEADD(week, - 5, GETDATE()) AND
    Recommend IS NOT NULL
GROUP BY
    Rating,
    UserId
HAVING
    COUNT(Rating) >= 1 AND UserId = '101'
ORDER BY
    Count DESC

This should restrict to records only occurring within the last five weeks, and also those records where Recommend is not NULL.

The reason you were getting an error is that by the time the HAVING clause is being evaluated, the database is dealing with aggregates of records. Since the CreatedDateTime field is not an aggregate, it resulted in an error.

Upvotes: 3

Related Questions