How to get row count ?

[Prem]

To all gurus,
I am developing an application in which i want to show the number
of rows returned by the query.
e.g.
Select Categories.Cate goryName, Products.Produc tName,
Sum(([Order Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100) AS
ProductSales
FROM
((([Order Details] INNER JOIN Orders ON [Order Details].OrderID =
Orders.OrderID)
INNER JOIN Products ON [Order Details].ProductID = Products.Produc tID)
INNER JOIN Categories ON Products.Catego ryID = Categories.Cate goryID)
WHERE
(Orders.Shipped Date) BETWEEN '1/1/1997' AND '12/31/1997'
GROUP BY
Categories.Cate goryName, Products.Produc tName

I want the number of rows returned by this query.
How can i get the number of rows?

Please help me..
waiting for your replies..

Prem
(premratan@hotm ail.com)

[Goetz Graefe]

Re: How to get row count ?

Select count (*) as "row count" from (select <any query here>) as t

For some reason, the final table alias "as t" is required.

Goetz Graefe


"Prem" <premratan@hotm ail.com> wrote in message
news:2f7d06ff.0 311111515.2a2a0 40c@posting.goo gle.com...[color=blue]
> To all gurus,
> I am developing an application in which i want to show the number
> of rows returned by the query.
> e.g.
> Select Categories.Cate goryName, Products.Produc tName,
> Sum(([Order Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100) AS
> ProductSales
> FROM
> ((([Order Details] INNER JOIN Orders ON [Order Details].OrderID =
> Orders.OrderID)
> INNER JOIN Products ON [Order Details].ProductID = Products.Produc tID)
> INNER JOIN Categories ON Products.Catego ryID = Categories.Cate goryID)
> WHERE
> (Orders.Shipped Date) BETWEEN '1/1/1997' AND '12/31/1997'
> GROUP BY
> Categories.Cate goryName, Products.Produc tName
>
> I want the number of rows returned by this query.
> How can i get the number of rows?
>
> Please help me..
> waiting for your replies..
>
> Prem
> (premratan@hotm ail.com)[/color]

[Anith Sen]

Re: How to get row count ?

Refer to @@ROWCOUNT in SQL Server Books Online. If you are using ADO in your
application, then you can use the recordset's RecordCount property to get
the value at the client side.

--
-- Anith
( Please reply to newsgroups only )

[Simon Hayes]

Re: How to get row count ?

premratan@hotma il.com (Prem) wrote in message news:<2f7d06ff. 0311111515.2a2a 040c@posting.go ogle.com>...[color=blue]
> To all gurus,
> I am developing an application in which i want to show the number
> of rows returned by the query.
> e.g.
> Select Categories.Cate goryName, Products.Produc tName,
> Sum(([Order Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100) AS
> ProductSales
> FROM
> ((([Order Details] INNER JOIN Orders ON [Order Details].OrderID =
> Orders.OrderID)
> INNER JOIN Products ON [Order Details].ProductID = Products.Produc tID)
> INNER JOIN Categories ON Products.Catego ryID = Categories.Cate goryID)
> WHERE
> (Orders.Shipped Date) BETWEEN '1/1/1997' AND '12/31/1997'
> GROUP BY
> Categories.Cate goryName, Products.Produc tName
>
> I want the number of rows returned by this query.
> How can i get the number of rows?
>
> Please help me..
> waiting for your replies..
>
> Prem
> (premratan@hotm ail.com)[/color]

After running the query, you can do this:

select @@rowcount

If you need to use the value later, you can put it in a variable:

set @rows = @@rowcount

Simon