mysql - When Statments with LIKE and Substrings -

i in school , trying figure out question asked instructor. have not asked instructor because gets me 2 weeks down road.

here questions asking:

using weblog table in database create query use when statement to:

categorize records origcmd field meeting following conditions categories should follows:

•category 1 = origcmd starts get

•category 2 = origcmd starts post

•category 3 = origcmd starts head

•category 4 = of left on records

•count records in each group •average hours part of origdate field without modifying field.

i have tried following without average because cannot pull correctly following:

select case  when origcmd "get%" "category 1" when origcmd like"post%" "category 2" when origcmd "head%" "category 3" else "category 4 of left on records" end "original cmd", count(*) total weblog  

when using pulls first when line , ignores rest except count. new @ , having difficult time trying understand mysql. think might need use substring cmd hoping little direction here might help.

regards,

updated:

select category,        case category when 1 'get' when 2 'post' when 3 'head' else 'other' end command,        count(*) rec_count,         round(avg(substring(origdate, 14, 2))) avg_hour (select w.*,          case when left(origcmd, 3) = 'get'  1              when left(origcmd, 4) = 'post' 2              when left(origcmd, 4) = 'head' 3         else 4 end category    weblog w) q  group category 

output:

| category | command | rec_count | avg_hour | --------------------------------------------- |        1 |     |         4 |       10 | |        2 |    post |         3 |       14 | |        3 |    head |         1 |       17 | |        4 |   other |         2 |        6 | 

sqlfiddle