Counting records in SQL query

Dear Experts,

I'm having a few issues counting the total number of records returned for this query.

I'm unable to change the query as it's generated by a system, but would like to be able to count the number of records returned as a COUNT() or otherwise.

I have tried and failed to count the correct number of records (3) returned by this query.

Could anybody point me in the right direction?

Thanks

Nick

SELECT DISTINCT OutletProductCustomFieldValues.Outlet_ID
FROM         AccountOutlets RIGHT OUTER JOIN
                      OutletProductCustomFieldValues_Historic ON AccountOutlets.Outlet_ID = OutletProductCustomFieldValues_Historic.Outlet_ID RIGHT OUTER JOIN
                      OutletProductCustomFieldValues INNER JOIN
                      CustomFields ON OutletProductCustomFieldValues.CustomField_ID = CustomFields.CustomField_ID ON 
                      OutletProductCustomFieldValues_Historic.CustomField_ID = OutletProductCustomFieldValues.CustomField_ID AND 
                      OutletProductCustomFieldValues_Historic.Product_ID = OutletProductCustomFieldValues.Product_ID
GROUP BY OutletProductCustomFieldValues.Outlet_ID, CustomFields.Company_ID, OutletProductCustomFieldValues_Historic.Modified, 
                      AccountOutlets.Account_ID, OutletProductCustomFieldValues.Product_ID, 
                      OutletProductCustomFieldValues_Historic.OutletCustomFieldValue_Value
HAVING      (CustomFields.Company_ID = 102) AND (OutletProductCustomFieldValues_Historic.Modified BETWEEN CONVERT(DATETIME, '11/03/2007', 102) AND 
                      CONVERT(DATETIME, '11/04/2009', 102)) AND (OutletProductCustomFieldValues.Product_ID = 51) AND (0 = 0) AND (AccountOutlets.Account_ID = 156) 
                      AND (OutletProductCustomFieldValues_Historic.OutletCustomFieldValue_Value = '160')

Open in new window

Microsoft SQL Server 2008Databases

Last Comment

Arthur_Wood

8/22/2022 - Mon

ASKER CERTIFIED SOLUTION

Arthur_Wood

4/10/2008

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

yuching

4/10/2008

Cant you just use @@ROWCOUNT in MS SQL?

chapmandew

4/10/2008

SELECT DISTINCT OutletProductCustomFieldValues.Outlet_ID
FROM AccountOutlets RIGHT OUTER JOIN
OutletProductCustomFieldValues_Historic ON AccountOutlets.Outlet_ID = OutletProductCustomFieldValues_Historic.Outlet_ID RIGHT OUTER JOIN
OutletProductCustomFieldValues INNER JOIN
CustomFields ON OutletProductCustomFieldValues.CustomField_ID = CustomFields.CustomField_ID ON
OutletProductCustomFieldValues_Historic.CustomField_ID = OutletProductCustomFieldValues.CustomField_ID AND
OutletProductCustomFieldValues_Historic.Product_ID = OutletProductCustomFieldValues.Product_ID
GROUP BY OutletProductCustomFieldValues.Outlet_ID, CustomFields.Company_ID, OutletProductCustomFieldValues_Historic.Modified,
AccountOutlets.Account_ID, OutletProductCustomFieldValues.Product_ID,
OutletProductCustomFieldValues_Historic.OutletCustomFieldValue_Value
HAVING (CustomFields.Company_ID = 102) AND (OutletProductCustomFieldValues_Historic.Modified BETWEEN CONVERT(DATETIME, '11/03/2007', 102) AND
CONVERT(DATETIME, '11/04/2009', 102)) AND (OutletProductCustomFieldValues.Product_ID = 51) AND (0 = 0) AND (AccountOutlets.Account_ID = 156)
AND (OutletProductCustomFieldValues_Historic.OutletCustomFieldValue_Value = '160')

SELECT NUmberofRowREturned = @@ROWCOUNT

LeskosekDenis

4/29/2008

Or just use a derived table like:

SELECT COUNT(t.Outlet_ID)AS Count
FROM (
		SELECT DISTINCT OutletProductCustomFieldValues.Outlet_ID
		FROM         AccountOutlets RIGHT OUTER JOIN
							  OutletProductCustomFieldValues_Historic ON AccountOutlets.Outlet_ID = OutletProductCustomFieldValues_Historic.Outlet_ID RIGHT OUTER JOIN
							  OutletProductCustomFieldValues INNER JOIN
							  CustomFields ON OutletProductCustomFieldValues.CustomField_ID = CustomFields.CustomField_ID ON 
							  OutletProductCustomFieldValues_Historic.CustomField_ID = OutletProductCustomFieldValues.CustomField_ID AND 
							  OutletProductCustomFieldValues_Historic.Product_ID = OutletProductCustomFieldValues.Product_ID
		GROUP BY OutletProductCustomFieldValues.Outlet_ID, CustomFields.Company_ID, OutletProductCustomFieldValues_Historic.Modified, 
							  AccountOutlets.Account_ID, OutletProductCustomFieldValues.Product_ID, 
							  OutletProductCustomFieldValues_Historic.OutletCustomFieldValue_Value
		HAVING      (CustomFields.Company_ID = 102) AND (OutletProductCustomFieldValues_Historic.Modified BETWEEN CONVERT(DATETIME, '11/03/2007', 102) AND 
							  CONVERT(DATETIME, '11/04/2009', 102)) AND (OutletProductCustomFieldValues.Product_ID = 51) AND (0 = 0) AND (AccountOutlets.Account_ID = 156) 
							  AND (OutletProductCustomFieldValues_Historic.OutletCustomFieldValue_Value = '160')
)t

Open in new window

Your help has saved me hundreds of hours of internet surfing.

fblack61

Arthur_Wood

5/17/2008

Glad to be of assistance.

AW