Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 18th, 2004, 06:55 PM
Authorized User
 
Join Date: Jul 2003
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
Default Aggregate with Group By

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.


 
Old March 19th, 2004, 05:58 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old March 19th, 2004, 06:10 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 231
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old March 19th, 2004, 03:23 PM
Authorized User
 
Join Date: Jul 2003
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?

 
Old March 29th, 2004, 08:39 AM
Registered User
 
Join Date: Mar 2004
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Restart new group number in Group Footer sukarso Crystal Reports 2 October 13th, 2006 12:11 PM
DataShaping with Aggregate Jon4Paz Classic ASP Databases 0 September 15th, 2006 01:29 PM
Aggregate Concatenation 12th_Man SQL Language 4 January 10th, 2006 03:26 PM
Aggregate Function Adamcg Access 3 December 5th, 2005 09:24 AM
Aggregate Count also need o sizemore SQL Language 2 November 22nd, 2004 04:28 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.