Group by partial string? Case with "Like"? Help!

[Solo4357]

I have a database that I need to run a group by with. I need to add up all the records by Department. Typical data:

Amount Department
10 01ED
14.50 01
10.44 02GX
100.00 01GX
300.00 03ED
400.00 02

And so on. The departments are the first two characters..
01, 02, 03, etc.

I'd like to get results like this:

124.50 01
410.44 02
300.00 03

I thought a select like this:

select sum(Amount) as Amt, (Case Dept when like '01%' then 01 when like '02%' then 02, when like '03%' then 03 else other end) as DeptCode
From Invoice

But Like doesn't work in a case statement. Any ideas?

 

[SQLDenis]

why not LEFT(Dept ,2) instead?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[SQLSister]

use left function or substring

Questions about posting. See faq183-874

 

[Solo4357]

Because... I didn't know about left in sql. Yeesh!

..
..

Ok I'm a newbie tard. It's my problem, I'm working on it. I'll try that. Thanks!

 

[gmmastros]

But Like doesn't work in a case statement. Any ideas?

You CAN use like in a case statement, just not the way you were doing it. The proper way is this...

[COLOR=blue]Select[/color] Sum(Amount) [COLOR=blue]As[/color] Amt,
       [COLOR=blue]Case[/color] [COLOR=blue]When[/color] Dept Like [COLOR=red]'01%'[/color] [COLOR=blue]Then[/color] [COLOR=red]'01'[/color]
            [COLOR=blue]When[/color] Dept Like [COLOR=red]'02%'[/color] [COLOR=blue]Then[/color] [COLOR=red]'02'[/color]
            [COLOR=blue]When[/color] Dept Like [COLOR=red]'03%'[/color] [COLOR=blue]Then[/color] [COLOR=red]'03'[/color]
            [COLOR=blue]End[/color] [COLOR=blue]As[/color] DeptCode
[COLOR=blue]From[/color]   Invoice
[COLOR=blue]Group[/color] [COLOR=blue]By[/color] [COLOR=blue]Case[/color] [COLOR=blue]When[/color] Dept Like [COLOR=red]'01%'[/color] [COLOR=blue]Then[/color] [COLOR=red]'01'[/color]
              [COLOR=blue]When[/color] Dept Like [COLOR=red]'02%'[/color] [COLOR=blue]Then[/color] [COLOR=red]'02'[/color]
              [COLOR=blue]When[/color] Dept Like [COLOR=red]'03%'[/color] [COLOR=blue]Then[/color] [COLOR=red]'03'[/color]
              [COLOR=blue]End[/color]

But, of course, using left is better because it would look like this...

[COLOR=blue]Select[/color] Sum(Amount) [COLOR=blue]As[/color] Amt,
       [COLOR=#FF00FF]Left[/color](Dept, 2) [COLOR=blue]As[/color] DeptCode
[COLOR=blue]From[/color]   Invoice
[COLOR=blue]Group[/color] [COLOR=blue]By[/color]  [COLOR=#FF00FF]Left[/color](Dept, 2)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom