Solved

How to filter the records in the SQL query ?

Posted on 2014-07-27
1
167 Views
Last Modified: 2014-07-27


http://sqlfiddle.com/#!3/dc7d4/2

I do not want to display code ='BBB'.  Where to add this condition in the following query ?



SELECT
      t1.City,t1.code
    , 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
              AND t2.code=t1.code
              ORDER BY t2.NumSold DESC, t2.Title
         
              FOR XML PATH ('')
              )
             , 1, 1, '')
         ) ca1 (Notes)
GROUP BY
  t1.City,t1.code
ORDER BY
      count(distinct title) DESC
    , sum(NumSold) DESC
    , City
;
0
Comment
Question by:Varshini S
1 Comment
 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
ID: 40223342
I would put it here:
SELECT
      t1.City,t1.code
    , 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
              AND t2.code=t1.code
              ORDER BY t2.NumSold DESC, t2.Title
          
              FOR XML PATH ('')
              )
             , 1, 1, '')
         ) ca1 (Notes)
WHERE t1.Code <> 'BBB'
GROUP BY
  t1.City,t1.code
ORDER BY
      count(distinct title) DESC
    , sum(NumSold) DESC
    , City
;

Open in new window

0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

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.
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…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

831 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