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.CategoryName, Products.ProductName,
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.ProductID)
INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID)
WHERE
(Orders.ShippedDate) BETWEEN '1/1/1997' AND '12/31/1997'
GROUP BY
Categories.CategoryName, Products.ProductName

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
(pr*******@hotmail.com)

[Goetz Graefe]

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" <pr*******@hotmail.com> wrote in message
news:2f**************************@posting.google.c om...

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.CategoryName, Products.ProductName,
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.ProductID)
INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID)
WHERE
(Orders.ShippedDate) BETWEEN '1/1/1997' AND '12/31/1997'
GROUP BY
Categories.CategoryName, Products.ProductName

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
(pr*******@hotmail.com)

[Anith Sen]

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]

pr*******@hotmail.com (Prem) wrote in message news:<2f**************************@posting.google. com>...

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.CategoryName, Products.ProductName,
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.ProductID)
INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID)
WHERE
(Orders.ShippedDate) BETWEEN '1/1/1997' AND '12/31/1997'
GROUP BY
Categories.CategoryName, Products.ProductName

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
(pr*******@hotmail.com)

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