• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 194
  • Last Modified:

How to filter the records in the SQL query ?



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
Varshini S
Asked:
Varshini S
1 Solution
 
chaauCommented:
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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now