distinct count

[phamyh]

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,

 

[ByteMyzer]

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];

 

[phamyh]

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?

 

[ByteMyzer]

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]
[B][COLOR=red]ORDER BY Count([Sum GT 2].[SUM]) DESC[/COLOR][/B];

Note that the ORDER BY clause is executed on the Expression Count([Sum GT 2].[SUM]), and not the Alias [Count].

 

[phamyh]

Ah. That makes sense. Thank you.