COUNT IN DATABASE
My database
NumberId jb1 jb2 jb3 jb4 jb5 jb6
01 1 2 0 5 7 9
02 2 2 2 1 0 9
03 0 2 1 6 6 7
select(
cast(case when jb1 = 1 then '1' else '0' end as int)+
cast(case when jb2 = 1 then '1' else '0' end as int)+
cast(case when jb3 = 1 then '1' else '0' end as int)+
cast(case when jb4 = 1 then '1' else '0' end as int)+
cast(case when jb5 = 1 then '1' else '0' end as int)+
cast(case when jb6 = 1 then '1' else '0' end as int) as '1'
cast(case when jb1 = 2 then '1' else '0' end as int)+
cast(case when jb2 = 2 then '1' else '0' end as int)+
cast(case when jb3 = 2 then '1' else '0' end as int)+
cast(case when jb4 = 2 then '1' else '0' end as int)+
cast(case when jb5 = 2 then '1' else '0' end as int)+
cast(case when jb6 = 2 then '1' else '0' end as int) as '2'
.......0
i have the following returns
NumberID 1 2 3 4 5 6 7 8 9 0
01 1 1 - - 1 - 1 - 1 1
02 1 3 - - - - - - 1 1
03 1 1 - - - 2 1 - - 1
hOW CAN I use select or other command to count from 1 to 0 where vlues => 1 as next
NumberID jbcount
01 6
02 4
03 5
Thanks
Software/Hardware used:
Software/Hardware used:
ASKED:
July 22, 2005 3:44 PM
UPDATED:
July 25, 2005 3:39 AM
Answer Wiki:
Hi,
First, you may use the select you already have as an inline
view for another select, like this:
select
numberid,
cast(case when c1 >= 1 then 1 else 0 end as int)+
cast(case when c2 >= 1 then 1 else 0 end as int)+
...
cast(case when c0 >= 1 then 1 else 0 end as int) as jbcount
from
(
select
numberid,
cast(case when jb1 = 1 then '1' else '0' end as int)+
cast(case when jb2 = 1 then '1' else '0' end as int)+
cast(case when jb3 = 1 then '1' else '0' end as int)+
cast(case when jb4 = 1 then '1' else '0' end as int)+
cast(case when jb5 = 1 then '1' else '0' end as int)+
cast(case when jb6 = 1 then '1' else '0' end as int) as c1,
cast(case when jb1 = 2 then '1' else '0' end as int)+
cast(case when jb2 = 2 then '1' else '0' end as int)+
cast(case when jb3 = 2 then '1' else '0' end as int)+
cast(case when jb4 = 2 then '1' else '0' end as int)+
cast(case when jb5 = 2 then '1' else '0' end as int)+
cast(case when jb6 = 2 then '1' else '0' end as int) as c2,
.......
as c0
from my_table
)
Of course, if your database supports such syntax.
What in fact you do here, is to count the distinct values
of your six columns jb1,jb2,jb3,jb4,jb5,jb6,
so you can do this also as follows, supposing that each
value is always one of 1,2,3,4,5,6,7,8,9,0.
select numberid,
1 +
cast(case when jb2 = jb1
then 0 else 1 end as int) +
cast(case when (jb3 = jb1 or jb3 = jb2)
then 0 else 1 end as int) +
cast(case when (jb4 = jb1 or jb4 = jb2 or jb4 = jb3)
then 0 else 1 end as int) +
...
a.s.o. up to jb6 as jbcount
--
from my_table
Hope this could help.
Rgds,
Iudith
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
