CODEWITHSUNDEEP
sqlsql-servergroup-by
Dolly Chan
Dolly Chan

Reputation: 103

SQL Server group by first then ungroup?

I have a list of data need to be grouped, but we only want to group data that count are greater than 3. 

AA
AA
BB
CCC
CCC
CCC

return

AA  1
AA  1
BB  1
CCC 3

Thank you for your help

Upvotes: 1

Views: 8790

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270703

Use the window functions for this:

select col, count(*) as cnt
from (select col, count(*) over (partition by col) as colcnt,
             row_number() over (order by (select NULL)) as seqnum
      from t
     ) t
 group by col, (case when colcnt < 3 then seqnum else NULL end)

This calculates the total count over the column and a unique identifier for each row. The group by clause then tests for the condition. If less than 3, then it uses the identifier to get each row. If greater, it uses a constant value (NULL) in this case.

Upvotes: 0

RichardTheKiwi
RichardTheKiwi

Reputation: 107786

select data, case when total < 3 then 1 else total end total
from
    (
        select data, Count(Data) Total
        from tbl
        group by data
    ) g
join (select 1 union all select 2) a(b)
  on a.b <= case when total < 3 then Total else 1 end
order by data

This should perform faster than LittleBobbyTables's answer most of the time.

Upvotes: 4

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32720

Off the top of my head, you could use a get a count of everything with a count greater than 2, and then use UNION ALL to get any records not in the first query:

SELECT 'AA' AS Data
INTO #Temp
UNION ALL SELECT 'AA'
UNION ALL SELECT 'BB'
UNION ALL SELECT 'CCC'
UNION ALL SELECT 'CCC'
UNION ALL SELECT 'CCC'

SELECT Data, COUNT(Data) AS MyCount
FROM #Temp
GROUP BY Data
HAVING COUNT(Data) > 2

UNION ALL

SELECT Data, 1
FROM #Temp
WHERE Data NOT IN (
    SELECT Data
    FROM #Temp
    GROUP BY Data
    HAVING COUNT(Data) > 2
)
ORDER BY Data

DROP TABLE #Temp

Upvotes: 2

Related Questions