Solved

Help w Sql Query

Posted on 2014-12-14
6
117 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
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…

775 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