How to Achieve this result using SQL query ?

I have the following records in books table

Table:
----------------------------------------------------------------
City                           Title                 No of Book Sold
-----------------------------------------------------------------
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


I need the below result.

Book5 sold in 4 cities so it is in the top one AND in Mumbai Book5 sold more number of copies 450000 so it is in first.
Book1 sold in 3 cities so it is in  2nd row
Book18 sold in 2 cities so it is in 3rd row
Is it possible to Achieve  the result using SQL query ?

Result:
------------------------------------------------------------------------------------------------------------------------------------------------
Title                City[No of book Sold]                                                                                Total Sold  
---------------------------------------------------------------------------------------------------------------------------------------------
Book5      Mumbai[450000],Chennai[35000],Delhi[25000],Bangalore[24000]        534000

Book1      Delhi[250000],Chennai[35000],Bangalore[24000]                                     309000  

Book18     London[18000],Bangalore[180]                                                                    18180        
   
Book3      Delhi[3200],Chennai[300]                                                                                3500

Book2      Mumbai[125000000]                                                                                        125000000
Book4      Bangalore[180}                                                                                                  180
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.

PortletPaulfreelancerCommented:
When I see this type of request I always get the sense that there is bound to be a better way. "I have the following records in books table" would appear to be a summary table from some prior group by query for example and the column heading "No of Book Sold" is a report label not something you want to use in queries.

However my ability to see into the unknown is very limited, so here is a query that will do what you want. I have not used "No of Book Sold" but you can do so if you change NumSold to [No of Book Sold]
 
SELECT
  t1.City
, max(ca1.notes) as "City[No of book Sold]"
, sum(NumSold)
FROM Table1 as t1
    CROSS APPLY (
        SELECT
        STUFF((
              SELECT
                    ', ' + t1.title+ '[' + convert(varchar,NumSold) + ']'
              FROM Table1 AS t2
              WHERE t2.City = t1.City
              FOR XML PATH ('')
              )
             , 1, 1, '')
         ) ca1 (Notes)
GROUP BY
  t1.City
;

Open in new window

Details
    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)
    ;

**Query 1**:

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

**[Results][2]**:
    
|      CITY |                               CITY[NO OF BOOK SOLD] |  COLUMN_2 |
|-----------|-----------------------------------------------------|-----------|
| Bangalore |  Book5[24000], Book5[180], Book5[24000], Book5[180] |     48360 |
|   Chennai |              Book5[35000], Book5[300], Book5[35000] |     70300 |
|     Delhi |            Book5[250000], Book5[3200], Book5[25000] |    278200 |
|    London |                                       Book18[18000] |     18000 |
|    Mumbai |                     Book5[125000000], Book5[450000] | 125450000 |

http://sqlfiddle.com/#!3/cc037/7

Open in new window

0
Varshini SAuthor Commented:
PortletPaul, Thank you so much for the query .This is what I need exactly!!
 I need couple of changes in this result.

London should go to last row ,since only one book sold in this city
Delhi needs to come to 2nd row since 278200 books sold . Compare to Chennai it is higher so it should be in the 2nd row.
To Achieve  this where to modify the script ?

|      CITY        |                               CITY[NO OF BOOK SOLD] |                              COLUMN_2 |
|----------------- |-----------------------------------------------------|-----------|--------------------------------------|
| Bangalore  |  Book5[24000], Book5[180], Book5[24000], Book5[180] |                  48360 |
|     Delhi        |            Book5[250000], Book5[3200], Book5[25000] |                      278200 |
|   Chennai    |              Book5[35000], Book5[300], Book5[35000] |                           70300 |
|    Mumbai    |                     Book5[125000000], Book5[450000] |                      125450000 |
|    London     |                                       Book18[18000] |                                                18000 |
0
PortletPaulfreelancerCommented:
by using the ORDER BY clause

Here (I think) you want :

the number of book titles (descending)
the number o books sold (descending)
City (only needed if there is a tie)

so:

SELECT
      t1.City
    , 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
              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

|      CITY |                               CITY[NO OF BOOK SOLD] | NO OF BOOK SOLD |
|-----------|-----------------------------------------------------|-----------------|
| Bangalore |  Book5[24000], Book5[180], Book5[24000], Book5[180] |           48360 |
|     Delhi |            Book5[250000], Book5[3200], Book5[25000] |          278200 |
|   Chennai |              Book5[35000], Book5[300], Book5[35000] |           70300 |
|    Mumbai |                     Book5[125000000], Book5[450000] |       125450000 |
|    London |                                       Book18[18000] |           18000 |
		

Open in new window

http://sqlfiddle.com/#!3/cc037/10
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PortletPaulfreelancerCommented:
I spotted an error, the query should be:
SELECT
      t1.City
    , 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

line 10 has changed and I also added order by into the concatenation of titles.
|      CITY |                                CITY[NO OF BOOK SOLD] | NO OF BOOK SOLD |
|-----------|------------------------------------------------------|-----------------|
| Bangalore |  Book1[24000], Book5[24000], Book18[180], Book4[180] |           48360 |
|     Delhi |             Book1[250000], Book5[25000], Book3[3200] |          278200 |
|   Chennai |               Book1[35000], Book5[35000], Book3[300] |           70300 |
|    Mumbai |                      Book2[125000000], Book5[450000] |       125450000 |
|    London |                                        Book18[18000] |           18000 |

Open in new window

http://sqlfiddle.com/#!3/cc037/13
0
Varshini SAuthor Commented:
I am sorry, Title  needs to be first column in the result.

 Result:
 ------------------------------------------------------------------------------------------------------------------------------------------------
 Title                City[No of book Sold]                                                                                Total Sold  
 ---------------------------------------------------------------------------------------------------------------------------------------------
 Book5      Mumbai[450000],Chennai[35000],Delhi[25000],Bangalore[24000]        534000

 Book1      Delhi[250000],Chennai[35000],Bangalore[24000]                                     309000  

 Book18     London[18000],Bangalore[180]                                                                    18180        
   
 Book3      Delhi[3200],Chennai[300]                                                                                3500

 Book2      Mumbai[125000000]                                                                                        125000000
 Book4      Bangalore[180}                                                                                                  180
0
Varshini SAuthor Commented:
Thank You, I got it !!!!
0
Varshini SAuthor Commented:
Thank you  , Is it possible to add one more column in the result set.
Book Title Count

|      CITY       |      Book Title Count     |                     CITY[NO OF BOOK SOLD]                                          | NO OF BOOK SOLD |
|---------------  |--------------------         ------|---------------------------------------------------------------------------------       |-------------------------------|
| Bangalore |      4                                  |        Book1[24000], Book5[24000], Book18[180], Book4[180] |           48360                |
0
PortletPaulfreelancerCommented:
|      CITY | TITLE COUNT |                                CITY[NO OF BOOK SOLD] | NO OF BOOK SOLD |
|-----------|-------------|------------------------------------------------------|-----------------|
| Bangalore |           4 |  Book1[24000], Book5[24000], Book18[180], Book4[180] |           48360 |
|     Delhi |           3 |             Book1[250000], Book5[25000], Book3[3200] |          278200 |
|   Chennai |           3 |               Book1[35000], Book5[35000], Book3[300] |           70300 |
|    Mumbai |           2 |                      Book2[125000000], Book5[450000] |       125450000 |
|    London |           1 |                                        Book18[18000] |           18000 |
		

Open in new window

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

http://sqlfiddle.com/#!3/cc037/19
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
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
Microsoft SQL Server

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.