?
Solved

Need help for SQL query

Posted on 2014-08-29
5
Medium Priority
?
149 Views
Last Modified: 2014-08-29
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

0
Comment
Question by:Varshini S
  • 3
  • 2
5 Comments
 
LVL 53

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 400 total points
ID: 40292588
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
 
LVL 49

Accepted Solution

by:
PortletPaul earned 1600 total points
ID: 40292600
|      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
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 40292607
Looks like PortletPaul was faster than me :)
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40292632
sorry Vitor, it was one of mine from the past, & thought it was interesting

Kudos to the Top (2) though. Cheers.
0
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 40292635
No worries. I was still  trying to figure out the logic :)

Cheers
0

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Loops Section Overview
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

864 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