Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Help w Sql Query

Posted on 2014-12-14
6
Medium Priority
?
134 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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

Amazon Web Services EC2 Cheat Sheet

AWS EC2 is a core part of AWS’s cloud platform, allowing users to spin up virtual machines for a variety of tasks; however, EC2’s offerings can be overwhelming. Learn the basics with our new AWS cheat sheet – this time on EC2!

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

715 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