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? – Aaron Bertrand Jan 29 '17 at 16:24
  • each group must have only one string that contain page1 – Elsayed Jan 29 '17 at 16:36
  • Great! How is that enforced? – Aaron Bertrand Jan 29 '17 at 16:36
  • The data I work on have this rule ,only one row per group have string 'page1' – Elsayed Jan 29 '17 at 16:45

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;
| improve this answer | |

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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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