How to count the number of fields that satisfy the condition?

liyang1223

I now want to ask such a question:
I have a table like this: householder name, age 1, age 2, age 3, age 4, age 5, age 6
I now want to calculate the number of> 80 in age 1, age 2, ..., that is, age 1, if age 2 is greater than 90, it is even one, and count the number of conditions that meet the conditions in these 6 ages, plus The name of the head of the household is output to another table.
It is represented as follows:
Head of household name | Age1 | Age2 | Age3 | Age4 | Age5 | Age6
Zhang San | 80 | 50 | 52 | 92 | 20 | 10

Lee Si | 75 | 81 | 63 | 40 | 23 | 85

Primary 5 | 93 | 78 | 77 | 50 | 26 | 33

I now want to get this result:
Head of household name | 60 <age <75 | 75 <age <90 | age> 90

How to achieve it, thanks in advance!

wtyrambo

create table #temp
(
name varchar (10),
age1 int,
age2 int,
age3 int,
age4 int,
age5 int,
age6 int
)

select * from #temp

insert into #temp values ​​('张三', 80,50,52,92,20,10)
insert into #temp values ​​('李四', 75,81,63,40,23,85)
insert into #temp values ​​('Little Five', 93,78,77,50,26,33)


select aaa.name Name of the head of household, aaa.age6075 [60 <age <75], bbb.age7590 [75 <age <90], ccc.age90 [age> 90]
from
(
select a.name, isnull (b.age6075,0) age6075
from #temp a,
(
 select aa.name, isnull (count (*), 0) as age6075 from
 (
  select name, age1 as age6075
  from #temp
  where age1> = 60 and age1 <75
  union all
  select name, age2
  from #temp
  where age2> = 60 and age2 <75
  union all
  select name, age3
  from #temp
  where age3> = 60 and age3 <75
  union all
  select name, age4
  from #temp
  where age4> = 60 and age4 <75
  union all
  select name, age5
  from #temp
  where age5> = 60 and age5 <75
  union all
  select name, age6
  from #temp
  where age6> = 60 and age6 <75
 ) aa
 group by aa.name
) b
where a.name * = b.name
) aaa,
-----
(
select a.name, isnull (b.age7590,0) age7590
from #temp a,
(
 select aa.name, isnull (count (*), 0) as age7590 from
 (
  select name, age1 as age7590
  from #temp
  where age1> = 75 and age1 <90
  union all
  select name, age2
  from #temp
  where age2> = 75 and age2 <90
  union all
  select name, age3
  from #temp
  where age3> = 75 and age3 <90
  union all
  select name, age4
  from #temp
  where age4> = 75 and age4 <90
  union all
  select name, age5
  from #temp
  where age5> = 75 and age5 <90
  union all
  select name, age6
  from #temp
  where age6> = 75 and age6 <90
 ) aa
 group by aa.name
) b
where a.name * = b.name
) bbb,
----
(
select a.name, isnull (b.age90,0) age90
from #temp a,
(
 select aa.name, isnull (count (*), 0) as age90 from
 (
  select name, age1 as age90
  from #temp
  where age1> = 90
  union all
  select name, age2
  from #temp
  where age2> = 90
  union all
  select name, age3
  from #temp
  where age3> = 90
  union all
  select name, age4
  from #temp
  where age4> = 90
  union all
  select name, age5
  from #temp
  where age5> = 90
  union all
  select name, age6
  from #temp
  where age6> = 90
 ) aa
 group by aa.name
) b
where a.name * = b.name
) ccc
where aaa.name = bbb.name
and bbb.name = ccc.name

liyang1223

Thank you very much