CODEWITHSUNDEEP
sql
Marina
Marina

Reputation: 13

how to Group by a new Select statement

So this is my current situation.

I have a Table 'Test' with following attributes:

I need to count how many patients have done a test from a specific type (it's a 1 - * relationship between Patient and Test).

I solved it like this: 

SELECT COUNT(*) 
FROM Test 
WHERE (Type = 'GDS') 
GROUP BY PatientID;

And now I have to group those results.. ("How many Patients have done n tests")

SELECT COUNT(*) 
FROM Test 
WHERE (Type = 'GDS') 
GROUP BY 
(
    SELECT COUNT(*) 
    FROM Test WHERE (Type = 'GDS') 
    GROUP BY PatientID
);

Of course it's wrong, since I don't have a clue how to do it .. and I couldn't find anything on the web.

It's a SQL database in Visual Studio ... so I'm not sure if JOINS are going to work.. and if, how would you use it?

Upvotes: 1

Views: 112

Answers (3)

BeemerGuy
BeemerGuy

Reputation: 8269

Answering your question:

I need to Count how many How many patients have done a test from a specific type (it's a 1 - * relationship between Patient and Test).

This is the solution: 

SELECT COUNT(DISTINCT PatientID) FROM Test WHERE Type = 'GDS'

For example, if you have: 

ID | PatiantID | Type
1  | 71        | GDS
2  | 71        | FTP
3  | 53        | GDS

Then the code above will return 2, coz there are two patients who had the GDS test.

Upvotes: 0

Edper
Edper

Reputation: 9322

You could either:

 SELECT COUNT(*) as PatientCount 
 FROM Test 
 WHERE Type = 'GDS'

or with GROUP BY

 SELECT PatientID, COUNT(*) as PatientCount FROM Test 
 WHERE Type = 'GDS'
 GROUP BY PatientID

And if you want specific number of the Aggregate function COUNT() you could use HAVING instead of WHERE, like:

 SELECT PatientID, COUNT(*) as PatientCount FROM Test 
 WHERE Type = 'GDS'
 GROUP BY PatientID
 HAVING COUNT(*) = n tests

Upvotes: 0

Cyril Gandon
Cyril Gandon

Reputation: 17058

Is this homework?
You are searching for the HAVING clause, that permits you to filter an aggregate result:

SELECT PatientID
FROM Test 
WHERE Type = 'GDS'
GROUP BY PatientID
HAVING COUNT(*) = n

Upvotes: 3

Related Questions