Solved

Help w Sql Query

Posted on 2014-12-14
6
119 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSMS Opening Mode 9 18
SQL Quer 4 21
Error when creating a table from a function 6 17
SQL - Curser to do an insert based on a select 2 9
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
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.
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…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

808 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