I want to know how many rows the result is

use select @@ROWCOUNT after you run the query

select i.orderid,i.used,o.availability
from orderitems i
inner join orders o on o.orderid = i.orderid
right join payments y on o.orderid = y.orderid
inner join products p on i.productid = p.productid
where o.statusid in (9,10,90,180,260) and y.type is not null
and i.sku=4
and i.productid=64
order by i.orderid desc

select @@ROWCOUNT

Select allOpen in new window

select @@ROWCOUNT

or you just want the count only

select count(1) as 'Total Count'
from orderitems i
inner join orders o on o.orderid = i.orderid
right join payments y on o.orderid = y.orderid
inner join products p on i.productid = p.productid
where o.statusid in (9,10,90,180,260) and y.type is not null
and i.sku=4
and i.productid=64

select i.orderid,i.used,o.availability,count(1) as 'Total Count'
from orderitems i
inner join orders o on o.orderid = i.orderid
right join payments y on o.orderid = y.orderid
inner join products p on i.productid = p.productid
where o.statusid in (9,10,90,180,260) and y.type is not null
and i.sku=4
and i.productid=64


Msg 8120, Level 16, State 1, Line 1
Column 'orderitems.orderid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


I need the query and the rowcount in one query

you have an aggregate (count) without a group by.

can you tell us exactly what you are trying to acheive?

i have a query of which i return results

i want to know how many rows the results are

the number of rows = the number of i.orderid

select i.orderid,i.used,o.availability,(select count(*) from orderitems where orderid = i.orderid) as 'Total Count'
from orderitems i
inner join orders o on o.orderid = i.orderid
right join payments y on o.orderid = y.orderid
inner join products p on i.productid = p.productid
where o.statusid in (9,10,90,180,260) and y.type is not null
and i.sku=4
and i.productid=64

select i.orderid,i.used,o.availability,(select count(*) from orderitems where orderid = i.orderid) as 'Total Count'
from orderitems i
inner join orders o on o.orderid = i.orderid
right join payments y on o.orderid = y.orderid
inner join products p on i.productid = p.productid
where o.statusid in (9,10,90,180,260) and y.type is not null
and i.sku=4
and i.productid=64



there are 6 rows = 6 results  but 'total count' is 1


I need this in one query because I am using this query in php not sql server

can you show a sample of your data and the expected result?

select i.orderid,i.used,o.availability,(select count(*) from orderitems) as 'Total Count'
from orderitems i
inner join orders o on o.orderid = i.orderid
right join payments y on o.orderid = y.orderid
inner join products p on i.productid = p.productid
where o.statusid in (9,10,90,180,260) and y.type is not null
and i.sku=4
and i.productid=64 

Select allOpen in new window

select i.orderid,i.used,o.availability,(select count(*) from orderitems) as 'Total Count'
from orderitems i
inner join orders o on o.orderid = i.orderid
right join payments y on o.orderid = y.orderid
inner join products p on i.productid = p.productid
where o.statusid in (9,10,90,180,260) and y.type is not null
and i.sku=4
and i.productid=64


now 'total count' is the number of rows in all of table orderitems

i want 'total count' to be the number of rows returned

Declare @Count int;

select @Count = Count(*)
from orderitems i
inner join orders o on o.orderid = i.orderid
right join payments y on o.orderid = y.orderid
inner join products p on i.productid = p.productid
where o.statusid in (9,10,90,180,260) and y.type is not null
and i.sku=4
and i.productid=64 ;

select i.orderid,i.used,o.availability,@Count as 'Total Count'
from orderitems i
inner join orders o on o.orderid = i.orderid
right join payments y on o.orderid = y.orderid
inner join products p on i.productid = p.productid
where o.statusid in (9,10,90,180,260) and y.type is not null
and i.sku=4
and i.productid=64 

Select allOpen in new window

Declare @Count int;

select @Count = Count(*)
from orderitems i
inner join orders o on o.orderid = i.orderid
right join payments y on o.orderid = y.orderid
inner join products p on i.productid = p.productid
where o.statusid in (9,10,90,180,260) and y.type is not null
and i.sku=4
and i.productid=64 ;

select i.orderid,i.used,o.availability,@Count as 'Total Count'
from orderitems i
inner join orders o on o.orderid = i.orderid
right join payments y on o.orderid = y.orderid
inner join products p on i.productid = p.productid
where o.statusid in (9,10,90,180,260) and y.type is not null
and i.sku=4
and i.productid=64





works... is there anyway to do it in one query

With(
select Count(*) as c
from orderitems i
inner join orders o on o.orderid = i.orderid
right join payments y on o.orderid = y.orderid
inner join products p on i.productid = p.productid
where o.statusid in (9,10,90,180,260) and y.type is not null
and i.sku=4
and i.productid=64) as cc
select i.orderid,i.used,o.availability,cc.c as 'Total Count'
from orderitems i
inner join orders o on o.orderid = i.orderid
right join payments y on o.orderid = y.orderid
inner join products p on i.productid = p.productid
where o.statusid in (9,10,90,180,260) and y.type is not null
and i.sku=4
and i.productid=64 

Select allOpen in new window

bitref:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '('.
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'as'.

emoreau:
works and displays the rowcount.  thank you