471,119 Members | 1,439 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,119 software developers and data experts.

trying to group by two diff criteria


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)

Feb 16 '07 #1
1 1985
dn*****@gmail.com (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****@sommarskog.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
Feb 16 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Jim Patterson | last post: by
2 posts views Thread by dolbz.cardiff | last post: by
9 posts views Thread by Ching-Lung | last post: by
4 posts views Thread by Andreas Kasparek | last post: by
3 posts views Thread by BarbaraB | last post: by
6 posts views Thread by Aaron Gray | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.