Aggregate with Group By

[email protected] · March 18th, 2004, 06:55 PM

Hello Everyone and thanks for your help in advance. I am working on a SPROC that uses the following query:

SELECT Insurance AS InsuranceName, COUNT(*) AS PatientCount
FROM tblPatientVisits
WHERE (CheckedIn BETWEEN @BeginningDate AND @EndingDate) AND (ProviderName <> 'Walked Out')
GROUP BY Insurance

This, of course returns the name of each insurance along with the corresponding count for each insurance. However, I would also like to return a total PatientCount along with the count for each insurance. Is there any way to do this in the same query or do I simply need to iterate through each group. Any help is greatly appreciated. Thanks.

pgtips · March 19th, 2004, 05:58 AM

You can just add WITH ROLLUP to the end of the statement, like this:

Code:

SELECT Insurance AS InsuranceName, COUNT(*) AS PatientCount
FROM  tblPatientVisits
WHERE (CheckedIn BETWEEN @BeginningDate AND @EndingDate) AND (ProviderName <> 'Walked Out')
GROUP BY Insurance WITH ROLLUP

then you will get an additional row where the InsuranceName column contains 'NULL' and the PatientCount column contains the "grand total".

If you have any nulls in your data, you will need to add a CASE statement to distinguish the actual Nulls from the "grand total" Null, like this:

Code:

SELECT CASE WHEN (GROUPING(Insurance) = 1) THEN 'TOTAL'
            ELSE COALESCE(Insurance, 'NULL') END AS InsuranceName,
       COUNT(*) AS PatientCount
FROM  tblPatientVisits
WHERE (CheckedIn BETWEEN @BeginningDate AND @EndingDate) AND (ProviderName <> 'Walked Out')
GROUP BY Insurance WITH ROLLUP

Just realised this is the general SQL Language forum, but what I've posted here applies to SQL Server. What RDBMS do you need this query for?

rgds
Phil

owain · March 19th, 2004, 06:10 AM

Your could also use a UNION query, Philâs query will probably work faster than a UNION one will, however as Phil said the WITH ROLLUP statement is SQL Server specific. Here is an example of a UNION query that will provide you with the results you require:

Code:

  SELECT Insurance AS InsuranceName, COUNT(*) AS PatientCount
    FROM tblPatientVisits
   WHERE (CheckedIn BETWEEN @BeginningDate AND @EndingDate)
     AND (ProviderName <> 'Walked Out')
GROUP BY Insurance

UNION ALL

  SELECT NULL AS InsuranceName, COUNT(*) AS PatientCount
    FROM tblPatientVisits
   WHERE (CheckedIn BETWEEN @BeginningDate AND @EndingDate)
     AND (ProviderName <> 'Walked Out')

Again you may need to add a CASE statement if your Insurance column contains NULL values.

Regards
Owain Williams

[email protected] · March 19th, 2004, 03:23 PM

Thanks to both. As far as Phil's question about what databse, I am indeed using SQL Server and accessing it through a VB.Net class, which could also present a problem. Since I am using ADO.Net (perhaps this is now outside the scope of this forum), how do I access the total count once it is returned? It would appear the the With the RollUp clause, is that simply appended as a final row in the data returned?

Andy Hodges · March 29th, 2004, 08:39 AM

I have a related problem. I am running this select:

SELECT max(datename(mm, dateenquiry)) As [Month],
    datepart(yyyy, Dateenquiry) AS [Year],
    count(name) as [Frequency],
    sum(ValueofClaim) as [Total Value],
    sum(Expences) as [Monthly Expense]

From JC_Entities

GROUP BY DATEPART(yyyy, Dateenquiry),
    DATEPART(m, dateenquiry)
ORDER BY DATEPART(yyyy, Dateenquiry),
    DATEPART(m, dateenquiry)

which works to give me the number of items and the totals of columns, but I now want to arrive at an AVG for each row and to perform a calculation say [Monthly Expense] / [Frequency].

I have failed to get this any further and I wanted to run it from an .aspx file as a stored procedure.

Any help would be appreciated.

Andy Hodges