|
|
Posted by Roy Harvey on 10/26/06 14:57
First the test tables and data.
CREATE TABLE Table1
(UserID int, UserSales int)
INSERT Table1 VALUES( 1, 10)
INSERT Table1 VALUES( 2, 13)
INSERT Table1 VALUES( 3, 17)
INSERT Table1 VALUES( 4, 19)
INSERT Table1 VALUES( 5, 21)
INSERT Table1 VALUES( 6, 10)
INSERT Table1 VALUES( 7, 12)
INSERT Table1 VALUES( 8, 11)
INSERT Table1 VALUES( 9, 31)
INSERT Table1 VALUES(10, 23)
INSERT Table1 VALUES(11, 24)
INSERT Table1 VALUES(12, 10)
INSERT Table1 VALUES(13, 16)
CREATE TABLE Table2
(UserID int, Country varchar(16))
INSERT Table2 VALUES ( 1, 'Canada')
INSERT Table2 VALUES ( 2, 'Canada')
INSERT Table2 VALUES ( 3, 'Canada')
INSERT Table2 VALUES ( 4, 'Canada')
INSERT Table2 VALUES ( 5, 'Canada')
INSERT Table2 VALUES ( 6, 'USA')
INSERT Table2 VALUES ( 7, 'USA')
INSERT Table2 VALUES ( 8, 'USA')
INSERT Table2 VALUES ( 9, 'USA')
INSERT Table2 VALUES (10, 'USA')
INSERT Table2 VALUES (11, 'UK')
INSERT Table2 VALUES (12, 'UK')
INSERT Table2 VALUES (13, 'UK')
Then the query:
SELECT C.Country, COALESCE(convert(char(6),X.UserID),'Others'),
SUM(S.UserSales) as TotalSales
FROM Table1 as S
JOIN Table2 as C
ON S.UserID = C.UserID
LEFT OUTER
JOIN (select A.UserID, A.Country
from Table2 as A
where UserID IN
(SELECT TOP 2 C.UserID
FROM Table1 as B
JOIN Table2 as C
ON B.UserID = C.UserID
WHERE A.Country = C.Country
GROUP BY C.UserID
ORDER BY SUM(B.UserSales) DESC)) X
ON C.Country = X.Country
AND C.UserID = X.UserID
GROUP BY C.Country, COALESCE(convert(char(6),X.UserID),'Others')
ORDER BY C.Country
Country TotalSales
---------------- ------ -----------
Canada 4 19
Canada 5 21
Canada Others 40
UK 11 24
UK 13 16
UK Others 10
USA 10 23
USA 9 31
USA Others 33
The order of the result set is left as an exercise for the original
poster.
Roy Harvey
Beacon Falls, CT
On 26 Oct 2006 06:44:37 -0700, amit.vasu@gmail.com wrote:
>Hello
>
>I am using sql server 2005.
>
>I have two tables as described below.
>
>Table1
>
>UserID UserSales
>---------------------
> 1 10
> 2 13
> 3 17
> 4 19
> 5 21
> 6 10
> 7 12
> 8 11
> 9 31
> 10 23
> 11 24
> 12 10
> 13 16
>
>Table2
>
>UserID Country
>----------------------
> 1 Canada
> 2 Canada
> 3 Canada
> 4 Canada
> 5 Canada
> 6 USA
> 7 USA
> 8 USA
> 9 USA
> 10 USA
> 11 UK
> 12 UK
> 13 UK
>
>
>I want to get top 2 UserSales for each country and remaining should be
>displayed as Total as Others for that country.
>
>Can someone please help me with this query?
>
>Regards
>
>Amit
Navigation:
[Reply to this message]
|