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_AnagraficaRivenditaand 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
