SQL Server - Counting Records Query SQL - Asked By N Bands on 27-Jan-06 07:10 AM

Hello, I have two tables. bands, and gigs.
Bands holds all our new bands, and gigs holds all our gigs.
I am trying to write a query which will list the last 5 added bands, and then ouput the number of gigs the band has. (band.id, is put into gigs.bandid)
I have this at the moment.
SELECT top 5 bands.id, bands.bandname, Count(*) AS intTotal
from bands
join gigs on
gigs.BandID=bands.id
WHERE gigdelete = 0 AND gigs.gigdate>=getdate()
group by bands.id, bands.bandname
order by bands.id desc
It works fine, however it only lists the last 5 added bands who have gigs. Rather than the top 5 bands, which may contain bands with no gigs.

Try this SQL statement:
SELECT A.ID, A.BandName, COUNT(*) AS intTotal
FROM (SELECT TOP 5 ID, BandName 
FROM Bands
ORDER BY ID) A LEFT OUTER JOIN Gigs B
ON A.ID = B.BandID AND
B.GigDelete = 0 AND
B.GigDate >= GETDATE()
GROUP BY A.ID, A.BandName
ORDER BY A.ID DESC

--query for 5 LAST ADDED Bands and their gig counts
SELECT TOP 5 b.Id, b.BandName, (SELECT COUNT(*) FROM gigs WHERE bandid=b.id and gigdelete=0 and gigdate>=getdate())  intTotal
FROM Bands b
ORDER BY   b.Id DESC
--query for 5 LAST ADDED Bands and their gig counts ORDERed by gig count
SELECT b.id, Bandname, COUNT(g.Id) intTotal 
FROM (SELECT TOP 5 Id, BandName FROM Bands ORDER BY ID DESC) b LEFT JOIN Gigs g ON g.BandId = b.Id AND gigdelete=0 AND gigdate>=getdate()
GROUP BY b.Id, Bandname
ORDER BY intTotal desc
You may want to consider using the first query if you do not need to sort by the number of gigs per band as the second query will cost you more.