Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT Count(*)
- from tblWhatever
- GROUP BY column_x [LIKE %Fall-2009%]
- --------
- BIOL-Fall_2009
- HIST Fall_2009
- BIOL Spring_2009
- ------
- Fall_2009 2
- Spring_2009 1
- -- 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
- -- 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),'')
- -- 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
- 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
- SELECT substring(column_x,5,100), count(*)
- FROM YourTable
- GROUP BY substring(column_x,5,100)
- SELECT COUNT(*)
- FROM tblWhatever
- GROUP BY column_x
- HAVING column_x LIKE '%Fall-2009%'
Add Comment
Please, Sign In to add comment