[thelist] case statement catch-all? MSSQL

[Brian Cummiskey]

one more from me today... i don't know what i'd do with out you guys 
sometimes :)

I'm working on the reporting side of the form I was working on earlier 
(and is working great thanks to your help!)

This may be a simple question, but here goes:

I need a breakdown by site.

No problem...

select
	case site when 'C' then 'CT' when 'P' then 'PA' when 'T' then 'TX' 
else null end as 'Site'
from
	table
group by
	site


works fine and dandy.

However, There is a 4th column-  "Overall" which includes all 3 sites. 
I'm not sure how to add that into my case statement.

I tried OR's, I tried when not null, and i tried IN(...) but it didn't 
like any of those.  Can this be done?

Thanks in advance for the tips.

-Brian

---------------------------------------------

and i think i owe the list a tip for my 3 questions today:

<tip type="SQL Math" author="Brian Cummiskey">
	When doing math on fields in a database, often times you have to do 
some tricky things to get the desired result.  For example, if you want 
to divide an integer by another integer, the result will return an 
integer.  4/7 is NOT an integer result, and thus, it will round to 
either 0 or 1.  You must cast either the numerator or the demoninator 
(or both if you wish) as a float (or double) so that the result will 
also be a float, and give you the expected .5714285 decimal.  Which 
leads me to the next issue--  rounding.

	I didn't type all of that number out, but it goes at least 12 
characters that my calculator supports.  Working with such numbers can 
tend to be tedious, and a lot of times, after you do the division, you 
want to display it as a percent.  It's not exactly straight forward, so 
here's a tip.

SELECT
cast(round((sum(cast(NUMERATOR_FIELD as float)) / 
sum(cast(DEMONINATOR_FIELD as float))) * 100,2) AS varchar) + '%' AS 
'NICE FORMATTED RESULT HEADER'
FROM your_table

This will give you a nice 57.14% as a result, in varchar form for easy 
string use.

remove the sum() if you aren't after the whole result set.  This can 
vary based on your query...

(this was tested with MSSQL and MySQL or oracle may have a slightly 
different syntax or roudning rules, so please keep that in mind)

Good luck :)
</tip>