hi i'm trying to do a distinct count of [SUM] but using the group by is not producing correct data.

SELECT [Sum GT 2].[MFG], [Sum GT 2].[MFG_CODE], Count([Sum GT 2].[SUM]) AS [Count]
FROM [Sum GT 2]
GROUP BY [Sum GT 2].[MFG], [Sum GT 2].[MFG_CODE];

how can i rewrite this to give me a distinct count?

thanks,

Try:
SELECT [Sum GT 2].[MFG], [Sum GT 2].[MFG_CODE],
Count([Sum GT 2].[SUM]) AS [Count]
FROM (SELECT DISTINCT [Sum GT 2].[MFG], [Sum GT 2].[MFG_CODE], [Sum GT 2].[SUM]
FROM [Sum GT 2]) AS [Sum GT 2]
GROUP BY [Sum GT 2].[MFG], [Sum GT 2].[MFG_CODE];

Try:
SELECT [Sum GT 2].[MFG], [Sum GT 2].[MFG_CODE],
Count([Sum GT 2].[SUM]) AS [Count]
FROM (SELECT DISTINCT [Sum GT 2].[MFG], [Sum GT 2].[MFG_CODE], [Sum GT 2].[SUM]
FROM [Sum GT 2]) AS [Sum GT 2]
GROUP BY [Sum GT 2].[MFG], [Sum GT 2].[MFG_CODE];


Thank you!! That works wonderfully! One additional question, how do I order desc for column count?

SELECT [Sum GT 2].[MFG], [Sum GT 2].[MFG_CODE],
Count([Sum GT 2].[SUM]) AS [Count]
FROM (SELECT DISTINCT [Sum GT 2].[MFG], [Sum GT 2].[MFG_CODE], [Sum GT 2].[SUM]
FROM [Sum GT 2]) AS [Sum GT 2]
GROUP BY [Sum GT 2].[MFG], [Sum GT 2].[MFG_CODE]
ORDER BY Count([Sum GT 2].[SUM]) DESC;
Note that the ORDER BY clause is executed on the Expression Count([Sum GT 2].[SUM]), and not the Alias [Count].

Ah. That makes sense. Thank you.