is it possible to make a query that select the TOP 3 customer by volume and present the other customers
in one chunk as others
Customer Volum
A 100
B 12
C 15
D 11
E 12
F 11
preferred result
C 15
B 12
E 12
Others 32
Thank
LasseThis is a multi-part message in MIME format.
--=_NextPart_000_02E3_01C3A9E4.F2818D00
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
One alternative:
select
Customer, Volume
from
(
select top 3
Customer, Volume
from
MyTable
order by
Volume desc
) as x
union all
select
'Others', sum (Volume)
from
MyTable
where
CustomerID not in
(
select top 3
CustomerID
from
MyTable
order by
Volume desc
)
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Lasse" <anonymous@.discussions.microsoft.com> wrote in message
news:B62AB6B1-096A-4BBC-9F19-C762B7E9AD89@.microsoft.com...
is it possible to make a query that select the TOP 3 customer by volume and
present the other customers
in one chunk as others?
Customer Volume
A 100
B 125
C 150
D 110
E 120
F 115
preferred result:
C 150
B 125
E 120
Others 325
Thanks
Lasse
--=_NextPart_000_02E3_01C3A9E4.F2818D00
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
One alternative:
select
Customer, Volume
from
(
select top 3
Customer, Volume
from
=MyTable
order by
Volume =desc
) as x
union all
select
'Others', =sum (Volume)
from
=MyTable
where
CustomerID =not in
(
select top 3
= CustomerID
=from
= MyTable
order =by
= Volume desc
)
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Lasse" wrote in message news:B62=AB6B1-096A-4BBC-9F19-C762B7E9AD89@.microsoft.com...is it possible to make a query that select the TOP 3 customer by volume and =present the other customers in one chunk as others?Customer &=nbsp; VolumeA 100 B 125C 150D 110E 120F =115preferred result:C 150B 125E 120Others 325ThanksLasse
--=_NextPart_000_02E3_01C3A9E4.F2818D00--|||Do:
SELECT Customer, SUM(Volume)
FROM (
SELECT CASE WHEN (SELECT COUNT(*)
FROM tbl t1
WHERE t1.Volume > tbl.Volume) < 3
THEN Customer ELSE 'Others'
END, Volume
FROM tbl ) D (Customer, Volume)
GROUP BY Customer ;
--
- Anith
( Please reply to newsgroups only )|||Tom,
I think, you'll have to re-write that along the lines of the following to
avoid the error on ORDER BY with UNION :
SELECT Customer, Volume
FROM ( SELECT TOP 3 Customer, Volume
FROM MyTable
ORDER BY Volume DESC ) AS x
UNION
SELECT 'Others', SUM(Volume)
FROM MyTable
WHERE NOT EXISTS (SELECT *
FROM ( SELECT TOP 3 Customer, Volume
FROM MyTable
ORDER BY Volume DESC ) AS x
WHERE x.Customer = tbl.Customer ) ;
--
- Anith
( Please reply to newsgroups only )|||This is a multi-part message in MIME format.
--=_NextPart_000_04B7_01C3A9FA.82C320D0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit
Agreed. It always pays to have DDL and INSERT's of sample data...
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:#JdTouhqDHA.1928@.TK2MSFTNGP12.phx.gbl...
Tom,
I think, you'll have to re-write that along the lines of the following to
avoid the error on ORDER BY with UNION :
SELECT Customer, Volume
FROM ( SELECT TOP 3 Customer, Volume
FROM MyTable
ORDER BY Volume DESC ) AS x
UNION
SELECT 'Others', SUM(Volume)
FROM MyTable
WHERE NOT EXISTS (SELECT *
FROM ( SELECT TOP 3 Customer, Volume
FROM MyTable
ORDER BY Volume DESC ) AS x
WHERE x.Customer = tbl.Customer ) ;
--
- Anith
( Please reply to newsgroups only )
--=_NextPart_000_04B7_01C3A9FA.82C320D0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Agreed. It always pays to have =DDL and INSERT's of sample data...
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Anith Sen" wrote in message news:#JdTouhqDHA.1928=@.TK2MSFTNGP12.phx.gbl...Tom,I think, you'll have to re-write that along the lines of the following =toavoid the error on ORDER BY with UNION :SELECT Customer, =Volume FROM ( SELECT TOP 3 Customer, Volume =FROM MyTable ORDER =BY Volume DESC ) AS x UNIONSELECT 'Others', =SUM(Volume) FROM MyTable WHERE NOT EXISTS (SELECT * &n=bsp; FROM ( SELECT TOP 3 Customer, Volume &nb=sp; &nbs=p; FROM MyTable &n=bsp; &nb=sp; ORDER BY Volume DESC ) AS x &n=bsp; WHERE x.Customer =3D tbl.Customer ) ;-- - Anith( Please =reply to newsgroups only )
--=_NextPart_000_04B7_01C3A9FA.82C320D0--
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment