Max/Count Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Max/Count Problem

Author

Topic

Spindles
Starting Member

1 Post

Posted - 2009-09-09 : 07:21:29

Hope you can help.

I'm trying to use COUNT and MAX to find the dominant Mosaic Type based on a count of households for an output area.

The query:

SELECT OA_CODE, MAX(FullTotals.Total) AS TopAnswer
FROM DIM_OUTPUT_AREA
INNER JOIN
(SELECT [Output Area (Pc)], [Mosaic UK Type (Pc)], COUNT(id) AS Total
FROM FACT_MOSAIC_NLPG
GROUP BY FACT_MOSAIC_NLPG.[Output Area (Pc)], [Mosaic UK Type (Pc)]) AS FullTotals
ON FullTotals.[Output Area (Pc)] = DIM_OUTPUT_AREA.OA_CODE
Group By OA_CODE
ORDER BY OA_CODE ASC

Returns the output area and the highest total. But when I add in the mosaic code like this:

SELECT OA_CODE, FullTotals.[Mosaic UK Type (Pc)], MAX(FullTotals.Total) AS TopAnswer
FROM DIM_OUTPUT_AREA
INNER JOIN
(SELECT [Output Area (Pc)], [Mosaic UK Type (Pc)], COUNT(id) AS Total
FROM FACT_MOSAIC_NLPG
GROUP BY FACT_MOSAIC_NLPG.[Output Area (Pc)], [Mosaic UK Type (Pc)]) AS FullTotals
ON FullTotals.[Output Area (Pc)] = DIM_OUTPUT_AREA.OA_CODE
Group By OA_CODE, FullTotals.[Mosaic UK Type (Pc)]
ORDER BY OA_CODE ASC

It returns ALL the codes associated with that output area instead of just the top one.

How do I make it return just the dominant type, output area code and the total for the highest count of ID's?

Cheers for any help.

Spindles

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources