get unique records with counts of duplicates?

[Techno]

so I have 2 tables which join together.

I want to know how many records on TrackAudit, between 2 dates, exist and the number of times they exist. how would I do this?

Table Tracks: ID, TrackName, TrackFileName, ArtistID, GenreID
Table Audit: ID, TrackID, DateOfEntry



So Track Audit will have multiple entries for a track but I want a list of each track, within a time period, showing the # of occurances for that track.

[jmcilhinney]

E.g.

SELECT Tracks.*, COUNT(Audit.TrackID) AS AuditCount
FROM Tracks
INNER JOIN Audit
ON Tracks.TrackID = Audit.TrackID
WHERE Audit.DateOfEntry BETWEEN @StartDate AND @EndDate
GROUP BY Tracks.*

That will only include those tracks that have at least one audit record in the period. If you want to include every track regardless then you'd have to use an LEFT OUTER JOIN. Also, I'm not sure that you can use a wildcard in the GROUP BY clause like that as I've never actually tried. You may have to write out the full column list but I'm not sure.

[Techno]

I tried something like that but it did not work - it still gave me the duplicates rather than counting the number of times that track appeared in the query/list