COUNT IN DATABASE

110 pts.
Tags:
Microsoft SQL Server 2000
SQL
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following