nkewney
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
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')
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Cant you just use @@ROWCOUNT in MS SQL?
SELECT DISTINCT OutletProductCustomFieldVa lues.Outle t_ID
FROM AccountOutlets RIGHT OUTER JOIN
OutletProductCustomFieldVa lues_Histo ric ON AccountOutlets.Outlet_ID = OutletProductCustomFieldVa lues_Histo ric.Outlet _ID RIGHT OUTER JOIN
OutletProductCustomFieldVa lues INNER JOIN
CustomFields ON OutletProductCustomFieldVa lues.Custo mField_ID = CustomFields.CustomField_I D ON
OutletProductCustomFieldVa lues_Histo ric.Custom Field_ID = OutletProductCustomFieldVa lues.Custo mField_ID AND
OutletProductCustomFieldVa lues_Histo ric.Produc t_ID = OutletProductCustomFieldVa lues.Produ ct_ID
GROUP BY OutletProductCustomFieldVa lues.Outle t_ID, CustomFields.Company_ID, OutletProductCustomFieldVa lues_Histo ric.Modifi ed,
AccountOutlets.Account_ID, OutletProductCustomFieldVa lues.Produ ct_ID,
OutletProductCustomFieldVa lues_Histo ric.Outlet CustomFiel dValue_Val ue
HAVING (CustomFields.Company_ID = 102) AND (OutletProductCustomFieldV alues_Hist oric.Modif ied BETWEEN CONVERT(DATETIME, '11/03/2007', 102) AND
CONVERT(DATETIME, '11/04/2009', 102)) AND (OutletProductCustomFieldV alues.Prod uct_ID = 51) AND (0 = 0) AND (AccountOutlets.Account_ID = 156)
AND (OutletProductCustomFieldV alues_Hist oric.Outle tCustomFie ldValue_Va lue = '160')
SELECT NUmberofRowREturned = @@ROWCOUNT
FROM AccountOutlets RIGHT OUTER JOIN
OutletProductCustomFieldVa
OutletProductCustomFieldVa
CustomFields ON OutletProductCustomFieldVa
OutletProductCustomFieldVa
OutletProductCustomFieldVa
GROUP BY OutletProductCustomFieldVa
AccountOutlets.Account_ID,
OutletProductCustomFieldVa
HAVING (CustomFields.Company_ID = 102) AND (OutletProductCustomFieldV
CONVERT(DATETIME, '11/04/2009', 102)) AND (OutletProductCustomFieldV
AND (OutletProductCustomFieldV
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
Glad to be of assistance.
AW
AW