complex count..

[tran008]

what is the best way to get the count of the columum if like the tree structure. If the first columun does not = 0 then go to the next columun and so on. if the columun match then count and go to the next row. eg:
t-x-y-z-w
2-3-4-5-6
0-2-0-0-0
2-1-0-3-5
0-0-0-1-1

count return for t=2 because row 1& 3 match,x=1 row 2 match,y=0 no row match,z=1 row 4 match,w1 no match.

thanks

 

[gmmastros]

When you think about it... Count is the same as sum when you use the value (1) for a match and the value (0) for a non-match, so... something like this may work out well.

[COLOR=blue]Declare[/color] @Temp 
[COLOR=blue]Table[/color]   ([COLOR=blue]t[/color] [COLOR=blue]int[/color], x [COLOR=blue]int[/color], y [COLOR=blue]int[/color], z [COLOR=blue]int[/color], w [COLOR=blue]int[/color])

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](2,3,4,5,6)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](0,2,0,0,0)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](2,1,0,3,5)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](0,0,0,1,1)

[COLOR=blue]Select[/color] Sum([COLOR=blue]Case[/color] [COLOR=blue]When[/color] [COLOR=blue]t[/color] = 2 [COLOR=blue]Then[/color] 1 [COLOR=blue]Else[/color] 0 [COLOR=blue]End[/color]) [COLOR=blue]As[/color] T_Matches,
       Sum([COLOR=blue]Case[/color] [COLOR=blue]When[/color] x = 1 [COLOR=blue]Then[/color] 1 [COLOR=blue]Else[/color] 0 [COLOR=blue]End[/color]) [COLOR=blue]As[/color] X_Matches,
       Sum([COLOR=blue]Case[/color] [COLOR=blue]When[/color] y = 0 [COLOR=blue]Then[/color] 1 [COLOR=blue]Else[/color] 0 [COLOR=blue]End[/color]) [COLOR=blue]As[/color] Y_Matches,
       Sum([COLOR=blue]Case[/color] [COLOR=blue]When[/color] z = 1 [COLOR=blue]Then[/color] 1 [COLOR=blue]Else[/color] 0 [COLOR=blue]End[/color]) [COLOR=blue]As[/color] Z_Matches,
       Sum([COLOR=blue]Case[/color] [COLOR=blue]When[/color] w = 1 [COLOR=blue]Then[/color] 1 [COLOR=blue]Else[/color] 0 [COLOR=blue]End[/color]) [COLOR=blue]As[/color] W_Matches
[COLOR=blue]From[/color]   @Temp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[tran008]

Hi george,

thanks for the reply. I just want to count the value of the columnun if it meet the criteria: If any column have a row that have value > 1 count them and go to the next row. If the columun row is =0, go to the next columun any value that is greater than 0, and so on or next row if it not match.

thanks

 

[tran008]

this is what I'm doign right now: step by step

select count(t) where t > 0
select count(x) where t = 0 and x >0
select count where t = 0 and x=0 and y >0
select count(x) where t=0 and x=0 and y=0 x >1

as you can see...this is not very effiecent.

thanks

 

[gmmastros]

As I see it, that code could be replaced with this...

[COLOR=blue]Declare[/color] @Temp 
[COLOR=blue]Table[/color]   ([COLOR=blue]t[/color] [COLOR=blue]int[/color], x [COLOR=blue]int[/color], y [COLOR=blue]int[/color], z [COLOR=blue]int[/color], w [COLOR=blue]int[/color])

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](2,3,4,5,6)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](0,2,0,0,0)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](2,1,0,3,5)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color](0,0,0,1,1)

[COLOR=blue]Select[/color] Sum([COLOR=blue]Case[/color] [COLOR=blue]When[/color] [COLOR=blue]t[/color] > 0 [COLOR=blue]Then[/color] 1 [COLOR=blue]Else[/color] 0 [COLOR=blue]End[/color]) [COLOR=blue]As[/color] T_Matches,
       Sum([COLOR=blue]Case[/color] [COLOR=blue]When[/color] [COLOR=blue]t[/color]=0 And X > 0 [COLOR=blue]Then[/color] 1 [COLOR=blue]Else[/color] 0 [COLOR=blue]End[/color]) [COLOR=blue]As[/color] X_Matches,
       Sum([COLOR=blue]Case[/color] [COLOR=blue]When[/color] [COLOR=blue]t[/color] = 0 and x = 0 And y > 0 [COLOR=blue]Then[/color] 1 [COLOR=blue]Else[/color] 0 [COLOR=blue]End[/color]) [COLOR=blue]As[/color] Y_Matches,
       Sum([COLOR=blue]Case[/color] [COLOR=blue]When[/color] [COLOR=blue]t[/color] = 0 and x = 0 and y = 0 And X > 1 [COLOR=blue]Then[/color] 1 [COLOR=blue]Else[/color] 0 [COLOR=blue]End[/color]) [COLOR=blue]As[/color] Z_Matches
[COLOR=blue]From[/color]   @Temp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[tran008]

thanks george