Row count from join statement

[neroe]

Hello

I have the following SQL;

select members.FIRSTNAME, members.LASTNAME, bookings.REF, bookings.EVENTNO, events.[DESC], memship.SHIPNO
from members
inner join bookings on members.REF = bookings.REF
inner join events on bookings.EVENTNO = events.EVENTNO
inner join memship on members.SHIPNO = memship.SHIPNO
where members.FIRSTNAME = 'Anna' AND members.LASTNAME = 'Howard'
group by members.FIRSTNAME, members.LASTNAME, bookings.REF, bookings.EVENTNO, events.[DESC], memship.SHIPNO

I get 69 records returned. How can I get just a count returned rather than all of the rows?

Thanks

 

[simian336]

with cte as
(
select members.FIRSTNAME, members.LASTNAME, bookings.REF,
bookings.EVENTNO, events.[DESC], memship.SHIPNO
from membersinner
join bookings
on members.REF = bookings.REFinner
join events
on bookings.EVENTNO = events.EVENTNOinner
join memship
on members.SHIPNO = memship.SHIPNO
where members.FIRSTNAME = 'Anna'
AND members.LASTNAME = 'Howard'
group by members.FIRSTNAME, members.LASTNAME, bookings.REF,
bookings.EVENTNO, events.[DESC], memship.SHIPNO
)
Select count(*)
from cte


It would be better to count an individual column rather then *. But the column needs to always be populated.

Simi

 

[neroe]

Thats fantastic, thank you.

 

[gmmastros]

It may be more efficient to write the query another way.

select Count(1) As CountOfRows
from members
inner join bookings 
    on members.REF = bookings.REFinner 
join events 
    on bookings.EVENTNO = events.EVENTNOinner 
join memship 
    on members.SHIPNO = memship.SHIPNO
where members.FIRSTNAME = 'Anna' 
    AND members.LASTNAME = 'Howard'

Notice that I am selecting Count(1) instead of all the columns and I removed the group by clause.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom