Posted by Erland Sommarskog on 02/16/07 22:44

dn.perl@gmail.com (dn.perl@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]