Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old March 18th, 2004, 06:55 PM
Authorized User
 
Join Date: Jul 2003
Location: , , .
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.


Reply With Quote
  #2 (permalink)  
Old March 19th, 2004, 05:58 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
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
Reply With Quote
  #3 (permalink)  
Old March 19th, 2004, 06:10 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Cardiff, , United Kingdom.
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
Reply With Quote
  #4 (permalink)  
Old March 19th, 2004, 03:23 PM
Authorized User
 
Join Date: Jul 2003
Location: , , .
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?

Reply With Quote
  #5 (permalink)  
Old March 29th, 2004, 08:39 AM
Registered User
 
Join Date: Mar 2004
Location: Nottingham, , United Kingdom.
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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 02:45 AM.


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