Solved

How to Achieve this result using  SQL  query ?

Posted on 2014-07-26
8
215 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
  • 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
 
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now