Solved

How to Achieve this result using  SQL  query ?

Posted on 2014-07-26
8
223 Views
Last Modified: 2014-07-27
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
0
Comment
Question by:Varshini S
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40222103
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
 

Author Comment

by:Varshini S
ID: 40222146
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40222149
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 48

Expert Comment

by:PortletPaul
ID: 40222154
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
 

Author Comment

by:Varshini S
ID: 40222160
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
 

Author Comment

by:Varshini S
ID: 40222164
Thank You, I got it !!!!
0
 

Author Comment

by:Varshini S
ID: 40222182
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
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40222203
|      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

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question