I am working with SQL Server 2012.

I want to get the count of column as well as the rows with specific condition in each group.

The query I have in mind looks something like this:

select count(column1), (column2 where column2 contains 'page1')
group by column1

I know that the above query isn't correct but I want to show the idea.

Sample data

column1   column2 
-------  --------
x1       'temp/page1_l'
x1       'temp/page2_f'
x2       'temp/page2_d'
x2       'temp/page1_k'
x2       'temp/page2_e'

Expected output

count(column1)  column2 
--------------  --------------
2               'temp/page1_l'
3               'temp/page1_k'

How can I achieve that output?

  • Can you explain why it's these specific rows you want (page1_l and page1_k)? In this simple example (only five rows), it is easy to see that they are the only ones with page1 in the string. But what if x1 had page1_l and page1_m? Commented Jan 29, 2017 at 16:24
  • each group must have only one string that contain page1
    – Elsayed
    Commented Jan 29, 2017 at 16:36
  • Great! How is that enforced? Commented Jan 29, 2017 at 16:36
  • The data I work on have this rule ,only one row per group have string 'page1'
    – Elsayed
    Commented Jan 29, 2017 at 16:45

2 Answers 2


Given this sample data:

CREATE TABLE #d ( column1 char(2), column2 varchar(32) );

INSERT #d (column1, column2) 
   VALUES ('x1',    'temp/page1_l'),
          ('x1',    'temp/page2_f'),
          ('x2',    'temp/page2_d'),
          ('x2',    'temp/page1_k'),
          ('x2',    'temp/page2_e');

One way to solve it is by taking the count separately:

;WITH agg AS
  SELECT column1, col1count = COUNT(*)
    FROM #d 
    GROUP BY column1
SELECT [count(column1)] = agg.col1count, filt.column2
  FROM agg INNER JOIN #d AS filt
    ON agg.column1 = filt.column1
  WHERE filt.column2 LIKE '%page1[_]%';

Or slightly differently:

  SELECT column1, column2, 
    column1count = COUNT(*) OVER (PARTITION BY column1)
  FROM #d
SELECT [count(column1)] = column1count, column2 
  FROM d
  WHERE column2 LIKE '%page1[_]%';

Another is along the lines of Rob's suggestion:

SELECT [count(column1)] = COUNT(column1),
    column2 = MIN(CASE WHEN column2 LIKE '%page1[_]%' THEN column2 END)
  FROM #d 
  GROUP BY column1;

For the (column2 where column2 contains 'page1') expression, try something like:

MAX(CASE WHEN Column2 LIKE '%page1%' THEN Column2 END)

...but I'm not sure what you expect if you have multiple rows with "page1" per column1.

  • 2
    And note that LIKE '%page1%' will count page1 but also page11, page100, page101, etc. Commented Jan 29, 2017 at 10:41
  • Sure. The question was just looking for that string...
    – Rob Farley
    Commented Jan 29, 2017 at 10:43
  • 1
    I didn't mean to criticize your answer, only note for the OP. Commented Jan 29, 2017 at 10:45
  • please @RobFarley I want to count on column1 as in the output
    – Elsayed
    Commented Jan 29, 2017 at 11:30
  • You can still put count(column1)
    – Rob Farley
    Commented Jan 29, 2017 at 11:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.