T-SQL - GROUP BY with LIKE - is this possible?

Question

Is there a way to include a LIKE expression in a GROUP BY query? For example:

  SELECT Count(*) 
    from tblWhatever
GROUP BY column_x [LIKE %Fall-2009%]

column_x:

--------
BIOL-Fall_2009
HIST Fall_2009
BIOL Spring_2009

Result:

------
Fall_2009   2
Spring_2009 1

Answer 1

I dont believe so, LIKE is effectively a binary state - something is LIKE or NOT LIKE, there are not logical degrees of 'likeness' that could be grouped together. Then again, I could be off my rocker.

If what you really want is to express filtering over your grouped data take a look at the HAVING clause.

http://msdn.microsoft.com/en-us/library/ms180199.aspx

Answer 2

LIKE does not make sense in your context, as it either matches or it does not, but it does not establish groups. You will have to use string functions that parse the column values into what makes sense for your data.

Answer 3

You can have it this way, but like the others said, doesn't really make sense:

SELECT COUNT(*) 
    FROM tblWhatever
GROUP BY column_x 
HAVING column_x LIKE '%Fall-2009%'

Answer 4

No, the LIKE function is not supported in the GROUP BY clause. You'd need to use:

  SELECT x.term,
         COUNT(*)
    FROM (SELECT CASE
                   WHEN CHARINDEX('Fall_2009', t.column) > 0 THEN
                     SUBSTRING(t.column, CHARINDEX('Fall_2009', t.column), LEN(t.column))
                   WHEN CHARINDEX('Spring_2009', t.column) > 0 THEN
                     SUBSTRING(t.column, CHARINDEX('Spring_2009', t.column), LEN(t.column))
                   ELSE
                     NULL
                 END as TERM
            FROM TABLE t) x
GROUP BY x.term

Answer 5

Unfortunately, you have a badly structured database, having combined SUBJECT and TERM into the same column. When you use GROUP BY it treats each unique value in the column as a group in the result set. You'd be best advised to restructure the database if at all possible — you probably want three columns here (SUBJECT, TERM, SCHOOL_YEAR) but two might possibly be appropriate.

If you can't restructure the database you'll need to parse the column to extract the term. Rexem showed you one way to do this, you could also use a stored procedure.

Answer 6

You need an expression that returns "Fall_2009" or "Spring_2009", and then group on that expression. eg:

-- identify each pattern individually w/ a case statement
SELECT
  CASE
    WHEN column_x LIKE '%Fall[_]2009'   THEN 'Fall 2009'
    WHEN column_x LIKE '%Spring[_]2009' THEN 'Spring 2009'
  END AS group_by_value
, COUNT(*) AS group_by_count
FROM Table1 a
GROUP BY 
  CASE
    WHEN column_x LIKE '%Fall[_]2009'   THEN 'Fall 2009'
    WHEN column_x LIKE '%Spring[_]2009' THEN 'Spring 2009'
  END

or

-- strip all characters up to the first space or dash
SELECT 
  STUFF(column_x,1,PATINDEX('%[- ]%',column_x),'') AS group_by_value
, COUNT(*) as group_by_count
FROM Table1 a
GROUP BY 
  STUFF(column_x,1,PATINDEX('%[- ]%',column_x),'')

or

-- join to a (pseudo) table of pattern masks
SELECT b.Label, COUNT(*)
FROM Table1 a
JOIN (
  SELECT '%Fall[_]2009'  , 'Fall, 2009' UNION ALL
  SELECT '%Spring[_]2009', 'Spring, 2009'
  ) b (Mask, Label) ON a.column_x LIKE b.Mask
GROUP BY b.Label

Answer 7

If your courses always take five letters, you can keep it really simple:

SELECT substring(column_x,5,100), count(*)
FROM YourTable
GROUP BY substring(column_x,5,100)

Otherwise, check Peters or Rexem's answer.

Answer 8

u can use tis code

SELECT Count(*)
from tblWhatever GROUP BY column_x having column_x LIKE '%Fall-2009%'

working in MS-SQL 2005

by

B Senthil Kumaran