?
Solved

GROUP BY Question

Posted on 2011-05-05
13
Medium Priority
?
486 Views
Last Modified: 2012-05-11
I have a large database which contains patient records.  The query joins 5 tables which is required to obtain required infornation to build a table.  The problem I am having is the 'account' table which holds individual patient visit information contains multiple entries for each patient.  I am trying to extract only the last visit for each patient.

I do receive the following error when executing the query "Msg 8120, Level 16, State 1, Line 1
Column 'Patients.LastName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."  

If I remove the GROUP BY clause the query runs but produces a table with all of the extra lines.


SELECT
Patients.PatientID, Patients.LastName, Patients.FirstName, Patients.MiddleInitial, Patients.Nickname, Patients.Address, Patients.Address2, Patients.City, Patients.State, Patients.Zip, Patients.BillToOverride, Patients.BillTo, Patients.BillToAddress, Patients.BillToAddress2, Patients.HomePhone, Patients.WorkPhone, Patients.WorkPhoneExt, Patients.CellPhone, Patients.Beeper, Patients.BeeperPIN, Patients.Fax, Patients.Email, Patients.EmailWork, Patients.Spouse, Patients.EmergencyContact, Patients.UserDefinedKey, Patients.Balance, Patients.Birthdate, Patients.SocialSecurity, Patients.StartDate, Patients.LastVisit, Patients.PrevVisit, Patients.ReferralByPatID, Patients.GeneralReferralKey, Patients.Reserved2, Patients.DefaultCase, Patients.ReferralSource, Patients.MailingLabelType, Patients.MaritalStatus, Patients.Gender, Patients.ReleasedFromCare, Patients.Deceased, Patients.Alerts, Patients.Inactive, Account.PatientID AS Expr1, Patients.LastName AS Expr2, 
Patients.FirstName AS Expr3, Patients.MiddleInitial AS Expr4, Patients.Address AS Expr5, Patients.City AS Expr6, Patients.Address2 AS Expr7, Patients.State AS Expr8, Patients.Zip AS Expr9, Patients.HomePhone AS Expr10, Patients.WorkPhone AS Expr11, Patients.SocialSecurity AS Expr12, Patients.Gender AS Expr13, Patients.Birthdate AS Expr14, Account.DoctorID AS Expr15, Doctors.OfficeID, Doctors.FirstName AS Expr16, Doctors.LastName AS Expr17, Doctors.Suffix, Doctors.Address AS Expr18, Doctors.City AS Expr19, Doctors.State AS Expr20, Doctors.Zip AS Expr21, 
Doctors.Phone AS Expr22, Patients.StartDate AS Expr23, Account.GuarantorID, Guarantors.LastName AS Expr24, Guarantors.FirstName AS Expr25, 
Guarantors.Address AS Expr26, Guarantors.Address2 AS Expr27, Guarantors.City AS Expr28, Guarantors.State AS Expr29, Guarantors.Zip AS Expr30, Guarantors.PolicyID, Guarantors.PayerContact, Payors.PayerName, Payors.Address AS Expr31, Payors.Address2 AS Expr32, Payors.City AS Expr33, Payors.Zip AS Expr34, Payors.State AS Expr35, Guarantors.GroupID, Guarantors.Deductible, Patients.CellPhone AS Expr36, Account.OfficeCode, 
Account.Date

FROM
Account INNER JOIN Patients ON Account.PatientID = Patients.PatientID INNER JOIN
Doctors ON Account.DoctorID = Doctors.DoctorID INNER JOIN
Guarantors ON Account.GuarantorID = Guarantors.GuarantorID INNER JOIN Payors ON Account.PayerID = Payors.PayerID

GROUP BY Patients.PatientID

ORDER BY Patients.PatientID, Account.Date

Open in new window

0
Comment
Question by:r_lynn
  • 3
  • 3
  • 2
  • +5
13 Comments
 
LVL 18

Expert Comment

by:sventhan
ID: 35701528
include all the columns in the select list to you group by.

or use  distinct

 select disticnt your columns from table ......
0
 
LVL 42

Expert Comment

by:dqmq
ID: 35701975
You need to reduce the account table to only the records of interest before joining to the other tables.
Select ...

FROM
(Select * from Account A1 
 inner join
   (Select ACCOUNT_PK,                    <--change to actual PK
      max(VISIT_DATE) VISIT_DATE          <--change to actual date
      group by ACCOUNT_PK        
    ) A2 on A1.ACCOUNT_PK = A2.ACCOUNT_PK 
) as Account


INNER JOIN Patients ON Account.PatientID = Patients.PatientID INNER JOIN 
Doctors ON Account.DoctorID = Doctors.DoctorID INNER JOIN 
Guarantors ON Account.GuarantorID = Guarantors.GuarantorID INNER JOIN Payors ON Account.PayerID = Payors.PayerID 
 
ORDER BY Patients.PatientID, Account.Date

Open in new window

0
 
LVL 42

Expert Comment

by:dqmq
ID: 35702004
PS. I don't think you need/want the GROUP BY which is introducing the error you reported.
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

Author Comment

by:r_lynn
ID: 35702194
Thank you both for your interest and input.  

What I am trying to accomplish is to referance 3 records from the accounts table and only the last record for the patient.  Using the GROUP BY command I thought it would give me the last resord of however many there may for each patient.

As I continue to work on this I have found a field in the patients table titled 'lastvisit'.

Would it be possible to create a temporary table joining the patients table and the accounts table first  and then run the remainder of the query?
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35702255
Sounds like what you want is this:

SELECT *
FROM (
   SELECT ...
        , ROW_NUMBER() OVER(PARTITION BY Patients.PatientID ORDER BY YourDateField DESC) RN
   FROM Account
   INNER JOIN Patients ON Account.PatientID = Patients.PatientID
   INNER JOIN Doctors ON Account.DoctorID = Doctors.DoctorID
   INNER JOIN Guarantors ON Account.GuarantorID = Guarantors.GuarantorID
   INNER JOIN Payors ON Account.PayerID = Payors.PayerID
) derived
WHERE RN = 1
ORDER BY PatientID, Date
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 35702298
The ROW_NUMBER() OVER(...) is the important bit of the above.
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_1555-Analytical-SQL-Where-do-you-rank.html

That might have been where dgmg was heading and perhaps just forgot the date in the JOIN.

(Select ACCOUNT_PK,                    <--change to actual PK
      max(VISIT_DATE) VISIT_DATE          <--change to actual date
      group by ACCOUNT_PK        
    ) A2 on A1.ACCOUNT_PK = A2.ACCOUNT_PK

Likely is supposed to be:

(Select ACCOUNT_PK,                    <--change to actual PK
      max(VISIT_DATE) VISIT_DATE          <--change to actual date
      group by ACCOUNT_PK        
    ) A2 on A1.ACCOUNT_PK = A2.ACCOUNT_PK AND A1.VISIT_DATE = A2.VISIT_DATE

Which will limit data to just the max visit date for each account/patient.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 35702755
>That might have been where dgmg was heading and perhaps just forgot the date in the JOIN.
That's correct, thx for the fix


>Using the GROUP BY command I thought it would give me the last resord of however many there may for each patient.
No, GROUP BY does not give you the last record.  Group By summarizes all the records in the group.

>What I am trying to accomplish is to referance 3 records from the accounts table and only the last record for the patient.

Now you've lost me.  What 3 records?  Which table contains the "last record for the patient" and how do you identify it.

BTW,  I didn't think of using PARTITION BY, but's a good alternative for selecting the "last record"
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35702980
Something like this perhaps:
SELECT	p.PatientID,
	p.LastName,
	p.FirstName,
	p.MiddleInitial,
	p.Nickname,
	p.[Address],
	p.Address2,
	p.City,
	p.[State],
	p.Zip,
	p.BillToOverride,
	p.BillTo,
	p.BillToAddress,
	p.BillToAddress2,
	p.HomePhone,
	p.WorkPhone,
	p.WorkPhoneExt,
	p.CellPhone,
	p.Beeper,
	p.BeeperPIN,
	p.Fax,
	p.Email,
	p.EmailWork,
	p.Spouse,
	p.EmergencyContact,
	p.UserDefinedKey,
	p.Balance,
	p.Birthdate,
	p.SocialSecurity,
	p.StartDate,
	p.LastVisit,
	p.PrevVisit,
	p.ReferralByPatID,
	p.GeneralReferralKey,
	p.Reserved2,
	p.DefaultCase,
	p.ReferralSource,
	p.MailingLabelType,
	p.MaritalStatus,
	p.Gender,
	p.ReleasedFromCare,
	p.Deceased,
	p.Alerts,
	p.Inactive,
	a.PatientID Expr1,
	p.LastName Expr2,
	p.FirstName Expr3,
	p.MiddleInitial Expr4,
	p.[Address] Expr5,
	p.City Expr6,
	p.Address2 Expr7,
	p.[State] Expr8,
	p.Zip Expr9,
	p.HomePhone Expr10,
	p.WorkPhone Expr11,
	p.SocialSecurity Expr12,
	p.Gender Expr13,
	p.Birthdate Expr14,
	a.DoctorID Expr15,
	d.OfficeID,
	d.FirstName Expr16,
	d.LastName Expr17,
	d.Suffix,
	d.[Address] Expr18,
	d.City Expr19,
	d.[State] Expr20,
	d.Zip Expr21,
	d.Phone Expr22,
	p.StartDate Expr23,
	a.GuarantorID,
	g.LastName Expr24,
	g.FirstName Expr25,
	g.[Address] Expr26,
	g.Address2 Expr27,
	g.City Expr28,
	g.[State] Expr29,
	g.Zip Expr30,
	g.PolicyID,
	g.PayerContact,
	y.PayerName,
	y.[Address] Expr31,
	y.Address2 Expr32,
	y.City Expr33,
	y.Zip Expr34,
	y.[State] Expr35,
	g.GroupID,
	g.Deductible,
	p.CellPhone Expr36,
	a.OfficeCode,
	a.[Date]
FROM	Account a
	INNER JOIN Patients p ON a.PatientID = p.PatientID
	INNER JOIN (
		SELECT	PatientID,
			MAX(lastvisit) MAXlastvisit
		FROM	Patients
		GROUP BY
			PatientID) px ON p.PatientID = px.PatientID AND p.lastvisit	= px.MAXlastvisit
	INNER JOIN Doctors d ON a.DoctorID = d.DoctorID
	INNER JOIN Guarantors g ON a.GuarantorID = g.GuarantorID
	INNER JOIN Payors y ON a.PayerID = y.PayerID
ORDER BY 
	p.PatientID,
	a.[Date]

Open in new window

0
 

Author Comment

by:r_lynn
ID: 35704022
OK, I am a novice at this so I have decided to layer on each piece one at a time, get working then add the next piece.

Below is the reduced query, if I remove the second INNER JOIN statement the query runs perfectly eliminating all duplicate patient demographic data.  When I add the second INNER JOIN I receive an error of  "Incorrect syntax near the keyword ORDER'.

I appreciate everyone input, thank you.

SELECT  P.PatientID,
               P.FirstName,
               P.MiddleInitial,
               P.LastName,
               P.[Address],
               P.City, P.[State],
               P.Zip,
               P.HomePhone,
               P.WorkPhone,
               P.SocialSecurity,
               P.Gender,
               P.Birthdate,
               P.LastVisit
FROM     Account a
      INNER JOIN
                         Patients p ON a.PatientID = P.PatientID AND A.Date = P.LastVisit
          INNER JOIN (
                            SELECT D.DoctorID, D.FirstName AS Expr1, D.LastName AS Expr2,  
                                                  D.Suffix,  D.Address AS Expr3, D.City AS Expr4, D.State AS Expr5,
                     D.Zip AS Expr6, D.Phone
                     FROM Doctors d
                                    WHERE D.DoctorID = A.DoctorID
                     GROUP BY D.DoctorID, expr1, expr2, d.suffix, expr3, expr4, expr5,
                                                       expr6, D.Phone
                       )
ORDER BY P.PatientID
0
 
LVL 9

Accepted Solution

by:
anillucky31 earned 2000 total points
ID: 35704134
This will remove you order by error.


SELECT  P.PatientID, 
               P.FirstName, 
               P.MiddleInitial, 
               P.LastName, 
               P.[Address], 
               P.City, P.[State], 
               P.Zip, 
               P.HomePhone, 
               P.WorkPhone, 
               P.SocialSecurity, 
               P.Gender, 
               P.Birthdate, 
               P.LastVisit
FROM     Account a 
      INNER JOIN
                         Patients p ON a.PatientID = P.PatientID AND A.Date = P.LastVisit 
          INNER JOIN (
                            SELECT DoctorID, FirstName AS Expr1, LastName AS Expr2,  
                                                  Suffix,  Address AS Expr3, City AS Expr4, State AS Expr5,
                     Zip AS Expr6, Phone 
                     FROM Doctors 
                                   
                     GROUP BY DoctorID, expr1, expr2, suffix, expr3, expr4, expr5, 
                                                       expr6, Phone
                       ) D ON d.doctorId = a.doctorid
ORDER BY P.PatientID

Open in new window

0
 
LVL 52

Expert Comment

by:_agx_
ID: 35704169
(no points ...)

Actually it doesn't look like you even need a derived query for the second join. Seems like a straight join w/ the "Doctors" table would do the same thing.

SELECT  P.PatientID,  ... other columns ....
FROM     Account a
                   INNER JOIN Patients p ON a.PatientID = P.PatientID AND A.Date = P.LastVisit
                   INNER JOIN Doctors d ON D.DoctorID = A.DoctorID
ORDER BY P.PatientID
0
 

Author Comment

by:r_lynn
ID: 35710427
Attached is a chart reflecting the tables and relationshipd this problem is working with.
NextGen-demographics-conversiomn.jpg
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35711030
Can you try this?
SELECT Patients.PatientID, 
       Patients.LastName, 
       Patients.FirstName, 
       Patients.MiddleInitial, 
       Patients.Nickname, 
       Patients.Address, 
       Patients.Address2, 
       Patients.City, 
       Patients.State, 
       Patients.Zip, 
       Patients.BillToOverride, 
       Patients.BillTo, 
       Patients.BillToAddress, 
       Patients.BillToAddress2, 
       Patients.HomePhone, 
       Patients.WorkPhone, 
       Patients.WorkPhoneExt, 
       Patients.CellPhone, 
       Patients.Beeper, 
       Patients.BeeperPIN, 
       Patients.Fax, 
       Patients.Email, 
       Patients.EmailWork, 
       Patients.Spouse, 
       Patients.EmergencyContact, 
       Patients.UserDefinedKey, 
       Patients.Balance, 
       Patients.Birthdate, 
       Patients.SocialSecurity, 
       Patients.StartDate, 
       Patients.LastVisit, 
       Patients.PrevVisit, 
       Patients.ReferralByPatID, 
       Patients.GeneralReferralKey, 
       Patients.Reserved2, 
       Patients.DefaultCase, 
       Patients.ReferralSource, 
       Patients.MailingLabelType, 
       Patients.MaritalStatus, 
       Patients.Gender, 
       Patients.ReleasedFromCare, 
       Patients.Deceased, 
       Patients.Alerts, 
       Patients.Inactive, 
       ACCOUNT.PatientID       AS Expr1, 
       Patients.LastName       AS Expr2, 
       Patients.FirstName      AS Expr3, 
       Patients.MiddleInitial  AS Expr4, 
       Patients.Address        AS Expr5, 
       Patients.City           AS Expr6, 
       Patients.Address2       AS Expr7, 
       Patients.State          AS Expr8, 
       Patients.Zip            AS Expr9, 
       Patients.HomePhone      AS Expr10, 
       Patients.WorkPhone      AS Expr11, 
       Patients.SocialSecurity AS Expr12, 
       Patients.Gender         AS Expr13, 
       Patients.Birthdate      AS Expr14, 
       ACCOUNT.DoctorID        AS Expr15, 
       Doctors.OfficeID, 
       Doctors.FirstName       AS Expr16, 
       Doctors.LastName        AS Expr17, 
       Doctors.Suffix, 
       Doctors.Address         AS Expr18, 
       Doctors.City            AS Expr19, 
       Doctors.State           AS Expr20, 
       Doctors.Zip             AS Expr21, 
       Doctors.Phone           AS Expr22, 
       Patients.StartDate      AS Expr23, 
       ACCOUNT.GuarantorID, 
       Guarantors.LastName     AS Expr24, 
       Guarantors.FirstName    AS Expr25, 
       Guarantors.Address      AS Expr26, 
       Guarantors.Address2     AS Expr27, 
       Guarantors.City         AS Expr28, 
       Guarantors.State        AS Expr29, 
       Guarantors.Zip          AS Expr30, 
       Guarantors.PolicyID, 
       Guarantors.PayerContact, 
       Payors.PayerName, 
       Payors.Address          AS Expr31, 
       Payors.Address2         AS Expr32, 
       Payors.City             AS Expr33, 
       Payors.Zip              AS Expr34, 
       Payors.State            AS Expr35, 
       Guarantors.GroupID, 
       Guarantors.Deductible, 
       Patients.CellPhone      AS Expr36, 
       ACCOUNT.OfficeCode, 
       ACCOUNT.DATE 
  FROM ACCOUNT 
       INNER JOIN Patients 
         ON ACCOUNT.PatientID = Patients.PatientID 
       INNER JOIN Doctors 
         ON ACCOUNT.DoctorID = Doctors.DoctorID 
       INNER JOIN Guarantors 
         ON ACCOUNT.GuarantorID = Guarantors.GuarantorID 
       INNER JOIN Payors 
         ON ACCOUNT.PayerID = Payors.PayerID 
 WHERE ACCOUNT.[Date] = (SELECT MAX(a1.[Date]) 
                           FROM ACCOUNT a1 
                          WHERE ACCOUNT.PatientID = a1.PatientID) 
 ORDER BY Patients.PatientID, 
          ACCOUNT.DATE 

Open in new window

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question