What fields must be in a GROUP BY when using avg, max? 2

[sbbrown9924]

I have this query where statistics are executed on various time intervals in a database that tracks ER patients.

I am a little fuzzy on using the GROUP BY and ORDER BY clauses. I must be putting the wrong fields in the GROUP BY clause because I am getting the same value for both avg and max.

Set objRSdischarge= Server.CreateObject("ADODB.Recordset")
strSQLdischarge = "SELECT TriageTime, DepartureTime, DispositionTime, InRoomTime, " & _
"avg(DateDiff('n',TriageTime, DepartureTime)) AS [avgLOSDischargedPatients], " & _
"max(DateDiff('n',TriageTime, DepartureTime)) AS [maxLOSDischargedPatients], " & _
"avg(DateDiff('n',InRoomTime, DispositionTime)) AS [avgInRoomToDispositionTime], " & _
"max(DateDiff('n',InRoomTime, DispositionTime)) AS [maxInRoomToDispositionTime], " & _
"avg(DateDiff('n',DispositionTime, DepartureTime)) AS [avgDispositionToDepartureTime] FROM Archive " & _
"WHERE (TriageTime BETWEEN #" & DateBegin & "# AND #" & DateEnd & "# ) AND (TriageAcuity BETWEEN 9 AND 31) " & _
"AND Disposition=10 GROUP BY TriageTime,DepartureTime,DispositionTime,InRoomTime"
objRSdischarge.Open strSQLdischarge, objConn, adOpenKeyset, adLockReadOnly, adCmdText

 

[cmmrfrds]

If you are averaging over the total resultset then remove the 4 times from the select list and the group by. What are you trying to do the aggregates over?

 

[sbbrown9924]

I don't need to aggregate in this query, but I read that all other columns not subject to avg, max, min, count, etc. needed to be aggregated. I took out the GROUP BY clause and explicit declarations of fields already in the avg/min/max(DateDiff('n',date1, date2 AS) alias statements. This seems to be working.

One question: I didn't declare Disposition or TriageAcuity in the SELECT statement but I am getting no error messages. Is this correct?

The query looks like this:

'ACUTE VISITS-PATIENT DISCHARGED
Set objRSdischarge= Server.CreateObject("ADODB.Recordset")
strSQLdischarge = "SELECT " & _
"avg(DateDiff('n',TriageTime, DepartureTime)) AS [avgLOSDischargedPatients], " & _
"max(DateDiff('n',TriageTime, DepartureTime)) AS [maxLOSDischargedPatients], " & _
"avg(DateDiff('n',InRoomTime, DispositionTime)) AS [avgInRoomToDispositionTime], " & _
"max(DateDiff('n',InRoomTime, DispositionTime)) AS [maxInRoomToDispositionTime], " & _
"avg(DateDiff('n',DispositionTime, DepartureTime)) AS [avgDispositionToDepartureTime], " & _
"max(DateDiff('n',DispositionTime, DepartureTime)) AS [maxDispositionToDepartureTime], " & _
"avg(DateDiff('n',InRoomTime, DepartureTime)) AS [avgInRoomToDepartureTime], " & _
"max(DateDiff('n',InRoomTime, DepartureTime)) AS [maxInRoomToDepartureTime] FROM Archive " & _
"WHERE (TriageTime BETWEEN #" & DateBegin & "# AND #" & DateEnd & "# ) AND (TriageAcuity BETWEEN 9 AND 31) " & _
"AND Disposition=10 AND InRoomTime<>#1/1/1900# AND DispositionTime<>#1/1/1900# AND DepartureTime<>#1/1/1900# "
objRSdischarge.Open strSQLdischarge, objConn, adOpenKeyset, adLockReadOnly, adCmdText

 

[Golom]

... I don't need to aggregate in this query

Yes you do ... and you are. In SQL "aggregate" means use a function that derives its results from a computation on one or more records (as opposed to a single record). You are using two aggregate functions (AVG and MAX). Others are COUNT, SUM, STDEV, MIN, FIRST, LAST, etc.

What you mean is that you don't need to GROUP in this query. When you GROUP in a query then your aggregate functions are computed for each unique combination of fields in the GROUP BY clause.

... is this correct?

It is correct SQL. Whether or not it is giving you the result that you want is a whole 'nother thing. This query will produce AVG amd MAX values in one record that summarize all records in the table that meet the WHERE clause criteria. If, for example, you wanted to see such results for each unique value of "Disposition" then include it in the SELECT and create a "GROUP BY Disposition" after the WHERE clause.

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]

 

[sbbrown9924]

ohhhhhhh.....

so I should aggregate on the aliases of avg/min/max??

 

[Golom]

so I should aggregate ...

You are already computing aggragates by using AVG and MAX.

"... aggregate on the alias ..." doesn't mean anything. The alias is just a column name that is assigned to the value being returned by the aggregate function.

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]

 

[sbbrown9924]

Sorry for my confusion here. Let me use one example of the statistics for this report.

The report will need the greatest length of time (max) and average time that a patient has had to wait in the waiting room. We're trying to get a handle on how long people are waiting in the ER waiting room. I'm pulling this interval DateDiff('n',TriageTime, InRoomTime) from a table of patient encounters. I'll also need the total amount of patients seen so I'll use COUNT in the example too.

So, I am looking for the greatest interval between TriageTime and InRoomTime from all patient enounters (given the constraints in the WHERE part of the query) and the average interval across all patient encounters.

So this part I think is correct so far:

SELECT COUNT(*), max(DateDiff('n',TriageTime, InRoomTime)), avg(DateDiff('n',TriageTime, InRoomTime))
FROM Archive
Where .....

So, does the query also need a GROUP BY so that its comparing all Tpatient encounter TriageTime-InRoomTime intervals? If so, what field or alias would I use? I think thats where I'm getting stuck. Thanks.

 

[Golom]

As I understand the issue ... no, you don't need a GROUP BY. As it is, the query is giving you the MAX and AVG values for all patient records that meet the criteria you've specified. If you were to specify some grouping via a GROUP BY clause then it would give you those statistics for each unique group.

Just as an example, if you had a field called "DoctorID" for example that specified the physician who treated the patient then

Select DoctorID,
       .... all the aggregate stuff ...
From   ...
Where  ...
GROUP BY DoctorID

Then you would see the same statistics that you are now generating but for each doctor. Without that field and GROUP BY, the results are for all patients and I believe that's what you want.

[small]On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question. (Charles Babbage)[/small]

 

[sbbrown9924]

Golum;

Thanks for setting me straight here. I think I understand this now.