Solved

How to Achieve this result using  SQL  query ?

Posted on 2014-07-26
8
222 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Activity Monitor detail 2 28
Are triggers slow? 7 14
Substring works but need to tweak it 14 16
Testing for Null in Stored Proc Return Value 5 15
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

830 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