Solved

Help w Sql Query

Posted on 2014-12-14
6
116 Views
Last Modified: 2015-03-24
Hi everyone,

I have a sql query and I need to add a piece of information to it which should be pretty easy for you experts!

The table in the query below (tblWorshipAttendance) has a record for each worship date.  I want to show a field in the results that also tells me the last date they attended.  The query right now tells me the persons name and how many times they attended during the year and I just want to add that last field that tells me the last time they attended.

SELECT tpm.FirstName, tpm.LastName, COUNT(twa.Contemporary1100) As AttendanceNos FROM tblPeopleMain tpm, tblWorshipAttendance twa WHERE twa.WorshipDate >= '01/01/2014' AND twa.WorshipDate <= '12/31/2014' AND Contemporary1100 = '1' AND tpm.RecordID = twa.PeopleID Group By tpm.LastName, tpm.FirstName
0
Comment
Question by:jwebster77
6 Comments
 
LVL 7

Accepted Solution

by:
Robert Sherman earned 500 total points
ID: 40498909
I believe you can just add a
  MAX(twa.WorshipDate) As LastAttended,
into your SQL statement above.

(Assuming that WorshipDate is the correct field you are looking for to show last date attended.)
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40498917
The above answer is correct.  Adding to your query, and re-formatted the query for readability:
SELECT 
   tpm.FirstName, 
   tpm.LastName, 
   COUNT(twa.Contemporary1100) As AttendanceNos, 
   MAX(twa.WorshipDate) As LastAttended
FROM tblPeopleMain tpm
   JOIN tblWorshipAttendance twa ON tpm.RecordID = twa.PeopleID 
WHERE twa.WorshipDate >= '2014-01-01' AND twa.WorshipDate <= '2014-12-31' AND Contemporary1100 = '1'
GROUP BY tpm.LastName, tpm.FirstName

Open in new window


Keep in mind that the way the query is written, if someone's LastAttended is before '2014-01-01', then they won't show in the query results.  If you need them to show, let us know and we'll provide that code.

btw I have an article called SQL Server GROUP BY Solutions that is a demo and image-heavy tutorial on GROUP BY.
0
 
LVL 7

Expert Comment

by:Robert Sherman
ID: 40498928
This is just an aside, but just in case it hadn't crossed your mind...

 with the grouping that you are doing based on LastName and FirstName you might run into trouble if you have different people with the same first and last name (e.g. father/son).    In those cases, you'd be grouping multiple individuals together and getting the aggregate results for each multiple-instance name.  

To avoid this, you could GROUP BY the key field from your 'tpm' table instead.
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:jwebster77
ID: 40498973
That is a good point on the grouping.  My revised query is this:

SELECT tpm.RecordID, 
   tpm.FirstName, 
   tpm.LastName, 
   COUNT(twa.Contemporary1100) As AttendanceNos, 
   MAX(twa.WorshipDate) As LastAttended
FROM tblPeopleMain tpm
   JOIN tblWorshipAttendance twa ON tpm.RecordID = twa.PeopleID 
WHERE twa.WorshipDate >= '2014-01-01' AND twa.WorshipDate <= '2014-12-31' AND Contemporary1100 = '1'
GROUP BY tpm.RecordID

Open in new window


I get an error that states:  Column 'tblPeopleMain.FirstName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

This is why I had put them in a grouping.  How can I get around the error above?
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40498980
>I get an error that states:  Column 'tblPeopleMain.FirstName' is invalid...
Any column you have in the SELECT clause that's not an aggregate needs to be in the GROUP BY clause.
So change your GROUP BY to...

GROUP BY tpm.RecordID, tpm.FirstName, tpm.LastName

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40499561
List all the non-aggregating fields in the GROUP BY clause (Lines 13-16)
Always include the table alias (TWA. was missing) (line 11)
While a date literal format of YYYY-MM-DD is reasonable, the safest date literal in SQL Server is YYYYMMDD (lines 9 & 10)
I recommend including INNER instead of just JOIN (line 8)

SELECT
      TPM.RecordID
    , TPM.FirstName
    , TPM.LastName
    , COUNT(TWA.Contemporary1100) AS ATTENDANCENOS
    , MAX(TWA.WorshipDate) AS LASTATTENDED
FROM tblPeopleMain TPM
      INNER JOIN tblWorshipAttendance TWA ON TPM.RecordID = TWA.PeopleID
WHERE TWA.WorshipDate >= '20140101'
AND TWA.WorshipDate <= '20141231'
AND TWA.Contemporary1100 = '1'
GROUP BY
      TPM.RecordID
    , TPM.FirstName
    , TPM.LastName
;

Open in new window


no points please
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now