sql server - SQL Cast TinyInt with Case Statement -

i writing query aggregates commonly reported on data. basically, i'm trying determine when resource has been used.

unfortunately, query returning error:

arithmetic overflow error converting expression data type tinyint.

insert utilization select resource        ,timesegment        ,case when min(uc.casemain_id) null 0 else count(uc.casemain_id) end inuse ... group resource, timesegment 

the error occurring on inuse , targeted column tiny int. have run query independently, , min , max values of count 0 , 4.

select min(inuse), max(inuse) (... query above ...) 

returns: 0, 4

any suggestions on why happening?

updates

sample of combination causes error:

create table usage(     [resource] [varchar](50) null,     [timesegment] [datetime] null,     [inuse] [tinyint] null );  create table timemap(     [timesegment] [datetime] null,     [resource] [varchar](50) null,     [case] [varchar](50) null,     [usage] [tinyint] null );  create procedure [dbo].[buildtimeusagemap] begin     truncate table utilization;     insert utilization     select resource            ,timesegment            ,count(uc.casemain_id) inuse       timemap     group resource, timesegment end;  exec buildtimeusagemap; 

i have altered select statement simplified (changed count(*) count(uc.casemain_id) return count of non-null values, thank-you @ughai ). have resolved issue changing data type of inuse smallint.

while changing datatype smallint makes problem go away, not explain why doesn't work in first place.

select min(inuse), max(inuse) usage 

results: 0,4

both of should fit in tinyint

i believe you're falling foul of sql server's illogical errors (where performs actions in different order logical processing order sql statements , raises errors not have been raised had followed logical order).

in such circumstance, real option split query multiple queries. note subqueries/ctes aren't enough guarantee optimizer won't move things around sufficiently cause these errors.

so would:

a) create temp table looser type constraints real table (i.e. uses int or bigint rather tinyint) , populate it. query should 1 performs aggregations , filters.

b) (optional) run query looking, out-of-range values , raise specific error messages found

c) insert temp table real table.