How to count rows in this query...

SELECT ID_AnagraficaRivendita
FROM dbo.AnagraficaRivendite
WHERE EXISTS
(SELECT *
FROM dbo.Flussi_Rivendite
WHERE dbo.Flussi_Rivendite.CodiceProdotto = 631 AND dbo.AnagraficaRivendite.ID_AnagraficaRivendita = dbo.Flussi_Rivendite.ID_AnagraficaRivendita)
AND EXISTS
(SELECT *
FROM dbo.Flussi_Rivendite
WHERE dbo.Flussi_Rivendite.CodiceProdotto = 615 AND dbo.AnagraficaRivendite.ID_AnagraficaRivendita = dbo.Flussi_Rivendite.ID_AnagraficaRivendita)
GROUP BY ID_AnagraficaRivendita

hi, in this query (in which I extract all ID_AnagraficaRivendita who have a correspondence in table Flussi_Rivendite with CodiceProdotto = 631 AND CodiceProdotto = 615), I would like to receive also a count of extracted rows... have you any idea?? Thank you ;)

There are so many ways like using COUNT(). But I always prefer usingROW_NUMBER()

e.g.

SELECT ROW_NUMBER()OVER (ORDER BY ID_AnagraficaRivenditaASC)AS Row, ID_AnagraficaRivenditaFROM dbo.AnagraficaRivenditeWHEREEXISTS(SELECT *FROM dbo.Flussi_RivenditeWHERE dbo.Flussi_Rivendite.CodiceProdotto = 631AND dbo.AnagraficaRivendite.ID_AnagraficaRivendita = dbo.Flussi_Rivendite.ID_AnagraficaRivendita)AND EXISTS(SELECT *FROM dbo.Flussi_RivenditeWHERE dbo.Flussi_Rivendite.CodiceProdotto = 615AND dbo.AnagraficaRivendite.ID_AnagraficaRivendita = dbo.Flussi_Rivendite.ID_AnagraficaRivendita)GROUP BY ID_AnagraficaRivendita

|||

try this (it was not tested but it should give you idea if it will not work)

SELECT ID_AnagraficaRivendita, FL.CodiceProdotto,count(*) Counter

FROM dbo.AnagraficaRivendite

LeftjoinFROM dbo.Flussi_Rivendite FL

ON dbo.AnagraficaRivendite.ID_AnagraficaRivendita= FLID_AnagraficaRivendita

and FL.CodiceProdottoin(631,615)

wherenot FL.CodiceProdottoisnull

groupby ID_AnagraficaRivendita, FL.CodiceProdotto

|||

Thank you for your reply, I have added ROW_NUMBER()OVER (ORDER BY ID_AnagraficaRivenditaASC)AS Row but it doesn't work, I receive an error (ADO Error: ROW_Number is not a valid function)

I don't know if is important: I am working on Sql Server 2000 and my query is in a Stored Procedure.

|||

RETURN @.@.ROWCOUNT

http://msdn2.microsoft.com/en-us/library/ms187316.aspx

|||

row_number is new in SQL 2005, not available in SQL 2000

|||

At my home pc I have just tried instruction ROW_NUMBER on SQL Server 2005 and it runs, but maybe my question it was not clear, sorry: I need TOTAL of extracted rows, on the contray with ROW_NUMBER I receive a progressive numeration of record (if it's possible, please suggest me a solution compatible on SQL Server 2000, too)

|||

Change your stored procedure into this:

SELECTCOUNT(ID_AnagraficaRivendita)AS TotalRows, ID_AnagraficaRivenditaFROM dbo.AnagraficaRivenditeWHEREEXISTS(SELECT *FROM dbo.Flussi_RivenditeWHERE dbo.Flussi_Rivendite.CodiceProdotto = 631AND dbo.AnagraficaRivendite.ID_AnagraficaRivendita = dbo.Flussi_Rivendite.ID_AnagraficaRivendita)AND EXISTS(SELECT *FROM dbo.Flussi_RivenditeWHERE dbo.Flussi_Rivendite.CodiceProdotto = 615AND dbo.AnagraficaRivendite.ID_AnagraficaRivendita = dbo.Flussi_Rivendite.ID_AnagraficaRivendita)GROUP BY ID_AnagraficaRivendita

Howevere let me also tell you thatthis is not at all an elegant solution. If you know how to work with return values, I suggest you addRETURN@.@.ROWCOUNT at the end of your original query, as suggested by DisturbedBuddha.

~

|||

thanks my friends