Link to home
Start Free TrialLog in
Avatar of nkewney
nkewneyFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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

ASKER CERTIFIED SOLUTION
Avatar of Arthur_Wood
Arthur_Wood
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of yuching
yuching

Cant you just use @@ROWCOUNT in MS SQL?
Avatar of chapmandew
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
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

Glad to be of assistance.

AW