trying to group by two diff criteria

[dn.perl]

I have a table t1 with two columns : c11 varchar(32) , c22 varchar(32)

The data in the table is :
'11', 'aa01'
and on upto
'11', 'aa50' : total 50 entries
'22', 'b01'
'22', b'02'
'22', b'03'
'33', 'c01' to '33', 'c40' : total 40 entries
'44', 'b02'
'44', 'd01'
'44', 'd01'
'44', 'd01'
How can write a query which will bunch together values of c11
with rows 5, and then bunch together values of c11 with
rows < 6, and add them up.
My output should be :
'11' 50
'33' 40
'others' 7 (3 rows for '22' and 4 for '44' are bunched
together
as the # of rows < 6, and added. 3+4 = 7)

[Erland Sommarskog]

dn*****@gmail.c om (dn*****@gmail. com) writes:

I have a table t1 with two columns : c11 varchar(32) , c22 varchar(32)

The data in the table is :
'11', 'aa01'
and on upto
'11', 'aa50' : total 50 entries
'22', 'b01'
'22', b'02'
'22', b'03'
'33', 'c01' to '33', 'c40' : total 40 entries
'44', 'b02'
'44', 'd01'
'44', 'd01'
'44', 'd01'
How can write a query which will bunch together values of c11
with rows 5, and then bunch together values of c11 with
rows < 6, and add them up.
My output should be :
'11' 50
'33' 40
'others' 7 (3 rows for '22' and 4 for '44' are bunched
together
as the # of rows < 6, and added. 3+4 = 7)

Here is a query that works in the Northwind database:

SELECT CustomerID, SUM(cnt) AS cnt
FROM (SELECT CASE WHEN cnt 7
THEN CustomerID
ELSE 'Others'
END AS CustomerID, cnt
FROM (SELECT CustomerID, COUNT(*) AS cnt
FROM Orders
GROUP BY CustomerID) AS a) AS b
GROUP BY CustomerID
ORDER BY CASE CustomerID WHEN 'Others' THEN 'ZZZZZZ' END,
cnt DESC

The query includes two derived tables. A derived is a virtual temp
table within the query so to speak. It's an entirely logical concept,
and the optimizer often recasts computation order, often resulting in
very efficient plans.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx