Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Help w Sql Query

Posted on 2014-12-14
6
Medium Priority
?
137 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 2000 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 66

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 does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 66

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 49

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

885 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