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

You need an expression that returns "Fall_2009" or "Spring_2009" and then is grouped into this 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 2

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

Answer 3

No, the LIKE function is not supported in the GROUP BY . You will 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 4

I don’t believe it, LIKE is actually a binary state - something LIKE or DO NOT LIKE, there are no logical degrees of "similarity" that can be grouped together. Again, I could disconnect from the video.

If you really want to filter by your grouped data, look at the HAVING clause.

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

Answer 5

If your courses always take up five letters, you can save them very simply:

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

Otherwise, check Peters or Rexem.

Answer 6

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

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

Answer 7

Unfortunately, you have a poorly structured database by combining SUBJECT and TERM in the same column. When you use GROUP BY, it treats each unique value in the column as a group in the result set. Restructuring the database is best recommended, if at all possible — you probably want three columns here (SUBJECT, TERM, SCHOOL_YEAR), but it is possible that this is possible.

If you cannot restructure the database, you need to analyze the column to extract this term. Rexem showed you one way to do this, you can also use a stored procedure.

Answer 8

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 

Answer 9

u can use tis code

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

works in MS-SQL 2005

by

B senthil kumaran