CODEWITHSUNDEEP
sql-server
Mikael
Mikael

Reputation: 982

Difficulty with counts

I have a table of encounters (relevant column names: enc_id (guid) and enc_nbr (int)) I have a table of encounter_notes (enc_id (guid))

I want to generate a list of enc_nbr where the related enc_id shows up in encounter_notes 10 times or more.

What I have tried:

select count(*) from encounters 
where enc_id in (select count(enc_id) from encounter_notes having count(enc_id)>9)

This gives the following error:

Operand type clash: uniqueidentifier is incompatible with int

Upvotes: 1

Views: 68

Answers (2)

digital.aaron
digital.aaron

Reputation: 5707

Maybe you're looking for something like this:

SELECT 
    enc_id
    ,NumberOfOccurances = COUNT(*) 
FROM ENCOUNTERS
GROUP BY enc_id
HAVING COUNT(*) > 9

The HAVING clause allows you to put a filter on an aggregate function, which you can't do with a WHERE clause. This query will return each enc_id and how many times it occurs, for all enc_id values that occur more than 9 times.

EDIT:

If you just want your code to run, you could change it to this:

select count(*) 
from encounters 
where enc_id in (select enc_id from encounter_notes having count(enc_id)>9)

Your subquery should return the set of GUIDs (enc_id) instead of the number of different values. But this isn't really good code. If possible, you should avoid subqueries in your WHERE clause like this, because the subquery ends up getting executed for every record in your outer query. This takes a long time.

Upvotes: 1

Nguyễn Văn Phong
Nguyễn Văn Phong

Reputation: 14208

You can't compare between a guid and a number.

You can also use CTE to achieve it.

;WITH cte AS
  (SELECT enc_id,
          count(enc_id)
   FROM encounter_notes
   GROUP BY enc_id
   HAVING count(enc_id)>9)
SELECT *
FROM encounters e
INNER JOIN cte ON e.enc_id = cte.enc_id

Upvotes: 2

Related Questions