Getting count(column) as well as a specific row in each group

Question

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?

Answer 1

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:

;WITH d AS
(
  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;

Answer 2

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.