By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,730 Members | 1,500 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,730 IT Pros & Developers. It's quick & easy.

using count to filter data

P: 15
I am trying to filter data using count. For a given day and patient, I would like to return all the valid tests. When I use a count this way, it only returns the patients with at least 4 tests. I want it to display those patients that have 1, 2, 3, or 4 valid tests, but not if 5, 6 or .... we taken on a given day. I only need the first four valid tests. Any suggesions?

SELECT Sheet1$.[Patient ID]
FROM M_PWA INNER JOIN
Sheet1$ ON M_PWA.DATETIME = Sheet1$.DATETIME
WHERE (Sheet1$.[Operator Index] >= 90)
GROUP BY Sheet1$.[Patient ID]
HAVING (COUNT(*) <= 4)
Jan 29 '07 #1
Share this Question
Share on Google+
4 Replies


iburyak
Expert 100+
P: 1,017
I am trying to filter data using count. For a given day and patient, I would like to return all the valid tests. When I use a count this way, it only returns the patients with at least 4 tests. I want it to display those patients that have 1, 2, 3, or 4 valid tests, but not if 5, 6 or .... we taken on a given day. I only need the first four valid tests. Any suggesions?

SELECT Sheet1$.[Patient ID]
FROM M_PWA INNER JOIN
Sheet1$ ON M_PWA.DATETIME = Sheet1$.DATETIME
WHERE (Sheet1$.[Operator Index] >= 90)
GROUP BY Sheet1$.[Patient ID]
HAVING (COUNT(*) <= 4)

Count looks good to me. Check your JOIN and WHERE condition.
Try to return * with no counts or Group by close
and order by Sheet1$.[Patient ID]. Just to make sure you have correct understanding of data that is counted.
Jan 29 '07 #2

P: 15
The patient test must be placed according to patient, and sorted by date (and time performed). My ultimate goal is to accept the first two tests above 90 or the first 4 tests above 80. If there are two 80's and then 2 90's, I want the 2 90's. Am I best off putting a row number in from of the 90's, a row number in front of the 80's and then accepting 2 90's or 4 80's? It is not working with a count function. I have a running row number, but that is cumulative, so that will not work either.
Jan 29 '07 #3

iburyak
Expert 100+
P: 1,017
The patient test must be placed according to patient, and sorted by date (and time performed). My ultimate goal is to accept the first two tests above 90 or the first 4 tests above 80. If there are two 80's and then 2 90's, I want the 2 90's. Am I best off putting a row number in from of the 90's, a row number in front of the 80's and then accepting 2 90's or 4 80's? It is not working with a count function. I have a running row number, but that is cumulative, so that will not work either.

Are you telling me that it could be count(*) = 6 but you accept only 4 first tests right? In this case your query is wrong.
Jan 29 '07 #4

P: 15
Yes, that is true
Jan 29 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.