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

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

SELECT Count(*) 
FROM开发者_StackOverflow社区 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

---

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

---

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

---

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.

---

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

---

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.

---

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%'

---

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.

---

How about this:

SELECT MAX(column_x) AS column_x 
FROM (
    SELECT column_x 
    FROM tblWhatever 
    WHERE (UPPER(column_x) LIKE  '%Fall-2009%')
) AS derivedtbl_1 GROUP BY column_x

---

This worked well for me

-- 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