Can someone help me fix this SELECT query.
Here's what I'm TRYING to do.
I have a table that stores several columns, one of which is a JobNumber. There could be multiple records through the table that have the same exact JobNumber.
I need to get a Count of all the records in the table that have the same JobNumber. However I also need to show only the distinct records from that table.
Here's what I tried to write but it doesn't provide what I'm looking for.
SELECT DISTINCT (e.JobNumber), e.ArrivalDate, e.EventName,
WHERE JobNumber = e.JobNumber) As NoOfRecords
FROM Events e
WHERE e.ArrivalDate > @TodaysDate
ORDER BY e.ArrivalDate
I want the results to look something like this:
JobNumber Arrival Date Event Name No Of Records
1 1/1/2011 Boss Meeting 15
2 1/15/2011 Client Meeting 8
How can I achieve this?