Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Row count from join statement

Status
Not open for further replies.

neroe

Technical User
Feb 27, 2003
54
GB
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
 
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
 
Thats fantastic, thank you.
 
It may be more efficient to write the query another way.

Code:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top