SQL Count syntax error

Question

I'm having trouble counting two things in an SQL query. I need to pull a list of files that have been accessed at least five times each by employees from both marketing and sales. I know I'm using COUNT incorrectly but I don't know how. Here's my query so far:

SELECT FileId
FROM Files
JOIN FileAccesses ON Files.FileId = FileAccesses.FileId
WHERE Count(AccessUserGroup=1)>5 AND Count(AccessUserGroup=2)>5

It produces the error

Incorrect syntax near ')'.

Files is a table with the int FileId as its primary key. FileAccesses stores FileId values from Files but not as the primary key. It keeps track of a bunch of metadata every time a user touches a file. For the purposes of this question, the part that matters is AccessUserGroup, a tinyint that is set to 1 for marketing and 2 for sales.

Use group by and having.

Gordon Linoff
– Gordon Linoff

2015-11-12 19:34:01 +00:00
Commented Nov 12, 2015 at 19:34 — Gordon Linoff
– Gordon Linoff, Commented Nov 12, 2015 at 19:34
Use HAVING COUNT(...) Instead

bksi
– bksi

2015-11-12 19:34:13 +00:00
Commented Nov 12, 2015 at 19:34 — bksi
– bksi, Commented Nov 12, 2015 at 19:34

Gordon Linoff · Accepted Answer · 2015-11-12 19:35:05Z

1

This is the query that you want:

SELECT fa.FileId
FROM FileAccesses fa 
GROUP BY fa.FileId
HAVING SUM(CASE WHEN AccessUserGroup = 1 THEN 1 ELSE 0 END) > 5 AND
       SUM(CASE WHEN AccessUserGroup = 2 THEN 1 ELSE 0 END) > 5;

Notes:

You don't need theJOIN, unless your FileAccesses could have a file id not in Files (which I consider unlikely).
You should be using GROUP BY if you want to use aggregation functions.
The comparisons go in the HAVING clause.
The COUNT() with an expression produces an error in SQL Server (it works in some other databases). SUM() with CASE does the conditional aggregation.

answered Nov 12, 2015 at 19:35

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mihail Shishkov · Accepted Answer · 2015-11-12 19:43:47Z

0

I guess you'll need the actual file info as well not just the file id, so you can use a cte (common table expression) https://msdn.microsoft.com/en-us/library/ms175972.aspx

    ;WITH cte AS (
    SELECT FileAccesses.FileId FROM FileAccesses 
    GROUP BY FileId
    HAVING COUNT(CASE AccessUserGroup WHEN 1 THEN 1 ELSE NULL END) > 5 
    AND COUNT(CASE AccessUserGroup WHEN 2 THEN 1 ELSE NULL END) > 5
    )
    SELECT * FROM File 
    INNER JOIN cte ON File.FileId = cte.FileId

answered Nov 12, 2015 at 19:43

Mihail Shishkov

16.2k8 gold badges52 silver badges63 bronze badges

Collectives™ on Stack Overflow

SQL Count syntax error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related