Need help for SQL query

I have the following query. I need to filter first two records in a separate column.

 CREATE TABLE Table1
    	([City] varchar(9), [Title] varchar(6), [NumSold] int)
    ;
    	
    INSERT INTO Table1
    	([City], [Title], [NumSold])
    VALUES
    	('Chennai', 'Book1', 35000),
    	('Delhi', 'Book1', 250000),
    	('Bangalore', 'Book1', 24000),
    	('Mumbai', 'Book2', 125000000),
    	('Chennai', 'Book3', 300),
    	('Delhi', 'Book3', 3200),
    	('Bangalore', 'Book4', 180),
    	('Chennai', 'Book5', 35000),
    	('Delhi', 'Book5', 25000),
    	('Bangalore', 'Book5', 24000),
    	('Mumbai', 'Book5', 450000),
    	('Bangalore', 'Book18', 180),
    	('London', 'Book18', 18000)
    ;

	SELECT
      t1.City
    , count(distinct title) as [Title Count]
    , max(ca1.notes)        as "City[No of book Sold]"
    , sum(NumSold)          as [NO OF BOOK SOLD]
FROM Table1 as t1
    CROSS APPLY (
        SELECT
        STUFF((
              SELECT
                    ', ' + t2.Title + '[' + convert(varchar,t2.NumSold) + ']'
              FROM Table1 AS t2
              WHERE t2.City = t1.City
          
              ORDER BY t2.NumSold DESC, t2.Title
          
              FOR XML PATH ('')
              )
             , 1, 1, '')
         ) ca1 (Notes)
GROUP BY
  t1.City
ORDER BY
      count(distinct title) DESC
    , sum(NumSold) DESC
    , City

Open in new window


Current output:
	City	              Title Count	City[No of book Sold]	                                                                     NO OF BOOK SOLD
1	Bangalore	4	                Book1[24000], Book5[24000], Book18[180], Book4[180]	                48360
2	Delhi	        3	                Book1[250000], Book5[25000], Book3[3200]	                                        278200
3	Chennai	        3	                Book1[35000], Book5[35000], Book3[300]	                                        70300
4	Mumbai	        2	                Book2[125000000], Book5[450000]                                                  	125450000
5	London	        1	                Book18[18000]	                                                                                        18000

Open in new window

I need a output like this:
	City	          Title Count   	Top 2 books                                      Remaining Books                                        SOLD
1	Bangalore	4	         Book1[24000], Book5[24000]              Book18[180], Book4[180]	                         48360
2	Delhi	        3	         Book1[250000], Book5[25000]            Book3[3200]	                                        278200
3	Chennai	        3	        Book1[35000], Book5[35000]               Book3[300]	                                                 70300
4	Mumbai	        2	        Book2[125000000], Book5[450000]                                                  	                125450000
5	London	        1	        Book18[18000]	                                                                                                         18000

Open in new window

Varshini SAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
If you had a TOP 2 in your subquery you'll have the Top 2 Books.
	SELECT
      t1.City
    , count(distinct title) as [Title Count]
    , max(ca1.notes)        as "City[No of book Sold]"
    , sum(NumSold)          as [NO OF BOOK SOLD]
FROM Table1 as t1
    CROSS APPLY (
        SELECT
        STUFF((
              SELECT TOP 2
                    ', ' + t2.Title + '[' + convert(varchar,t2.NumSold) + ']'
              FROM Table1 AS t2
              WHERE t2.City = t1.City
          
              ORDER BY t2.NumSold DESC, t2.Title
          
              FOR XML PATH ('')
              )
             , 1, 1, '')
         ) ca1 (Notes)
GROUP BY
  t1.City
ORDER BY
      count(distinct title) DESC
    , sum(NumSold) DESC
    , City

Open in new window



Now I need some time to see how to get the Remaining Books.
Will back to you when I find a solution.
0
PortletPaulfreelancerCommented:
|      CITY | TITLE COUNT |                      TOP 2 BOOKS |          REMAINING BOOKS | NO OF BOOK SOLD |
|-----------|-------------|----------------------------------|--------------------------|-----------------|
| Bangalore |           4 |       Book5[24000], Book1[24000] |  Book4[180], Book18[180] |           48360 |
|     Delhi |           3 |      Book5[25000], Book1[250000] |              Book3[3200] |          278200 |
|   Chennai |           3 |       Book5[35000], Book1[35000] |               Book3[300] |           70300 |
|    Mumbai |           2 |  Book5[450000], Book2[125000000] |                   (null) |       125450000 |
|    London |           1 |                    Book18[18000] |                   (null) |           18000 |

Open in new window

With this query
;WITH
      CTE
      AS (
                  SELECT
                        City
                      , Title
                      , NumSold
                      , ROW_NUMBER() OVER (PARTITION BY City
                        ORDER BY NumSold DESC, Title) AS rn
                  FROM Table1
            )
SELECT
      t1.City
    , COUNT(DISTINCT title) AS [Title Count]
    , MAX(ca1.TopTwo)       AS "Top 2 Books"
    , MAX(ca1.Remain)       AS "Remaining Books"
    , SUM(NumSold)          AS [NO OF BOOK SOLD]
FROM Table1 AS t1
      CROSS APPLY (
                  SELECT
                        STUFF((
                              SELECT
                                    ', ' + t2.Title + '[' + CONVERT(varchar, t2.NumSold) + ']'
                              FROM CTE AS t2
                              WHERE t2.City = t1.City
                                    AND t2.rn < 3
                              ORDER BY
                                    RN DESC
                              FOR xml PATH ('')
                        )
                        , 1, 1, '')
                      , STUFF((
                              SELECT TOP 2
                                    ', ' + t2.Title + '[' + CONVERT(varchar, t2.NumSold) + ']'
                              FROM CTE AS t2
                              WHERE t2.City = t1.City
                                    AND t2.rn > 2
                              ORDER BY
                                    RN DESC
                              FOR xml PATH ('')
                        )
                        , 1, 1, '')
            ) ca1 (TopTwo, Remain)
GROUP BY
      t1.City
ORDER BY
      COUNT(DISTINCT title) DESC
    , SUM(NumSold) DESC
    , City
;

Open in new window

http://sqlfiddle.com/#!3/31cba/5
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
Looks like PortletPaul was faster than me :)
0
PortletPaulfreelancerCommented:
sorry Vitor, it was one of mine from the past, & thought it was interesting

Kudos to the Top (2) though. Cheers.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
No worries. I was still  trying to figure out the logic :)

Cheers
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.