

Ten Centuries
Group: General Forum Members
Last Login: Monday, November 17, 2014 12:23 PM
Points: 1,360,
Visits: 1,761


Hello All,
I need help with a tsql query. Below is the table structure.
CREATE TABLE [dbo].[TableName]( [col1] [int] NOT NULL, [col2] [int] NOT NULL, [col3] [int] NOT NULL, [col4] [int] NOT NULL, [col5] [int] NOT NULL ) ON [PRIMARY]
GO
Select 1op 10* from tablename
col1 col2 col3 col4 col5 15 12 9 5 7 3 5 15 6 16 10 14 8 17 16 11 10 5 2 4 6 2 7 13 8 4 2 6 7 19 10 8 7 16 14 17 17 9 14 18 7 19 15 12 13 4 7 12 13 11
I need to get a count of negative numbers and positive in every row on a row by row basis. Thanks for your help in advance.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.”  John Quincy Adams




SSChasing Mays
Group: General Forum Members
Last Login: Yesterday @ 10:21 AM
Points: 605,
Visits: 3,537


SELECT CASE WHEN col1 < 0 THEN 1 ELSE 0 END + CASE WHEN col2 < 0 THEN 1 ELSE 0 END + CASE WHEN col3 < 0 THEN 1 ELSE 0 END + CASE WHEN col4 < 0 THEN 1 ELSE 0 END + CASE WHEN col5 < 0 THEN 1 ELSE 0 END AS CountNegs, CASE WHEN col1 >= 0 THEN 1 ELSE 0 END + CASE WHEN col2 >= 0 THEN 1 ELSE 0 END + CASE WHEN col3 >= 0 THEN 1 ELSE 0 END + CASE WHEN col4 >= 0 THEN 1 ELSE 0 END + CASE WHEN col5 >= 0 THEN 1 ELSE 0 END AS CountPos FROM TableName




Ten Centuries
Group: General Forum Members
Last Login: Monday, November 17, 2014 12:23 PM
Points: 1,360,
Visits: 1,761


Thanks for your help. It works like a charm
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.”  John Quincy Adams




SSCrazy
Group: General Forum Members
Last Login: 2 days ago @ 8:23 AM
Points: 2,873,
Visits: 5,189


There is another, more mathematical way: If no zero values in your columns:
select *, 5Positives as Negatives from (select *,(sign(col1)+sign(col2)+sign(col3)+sign(col4)+sign(col5)+5)/2 Positives from tablename) sn
And, if there are zero values:
select col1, col2, col3, col4, col5 ,(s1+abs(s1)+s2+abs(s2)+s3+abs(s3)+s4+abs(s4)+s5+abs(s5))/2 as Positive ,abs((s1abs(s1)+s2abs(s2)+s3abs(s3)+s4abs(s4)+s5abs(s5))/2) as Negative from (select *,sign(col1) s1,sign(col2) s2,sign(col3) s3,sign(col4) s4,sign(col5) s5 from tablename) sn
_____________________________________________ "The only true wisdom is in knowing you know nothing" "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!" (So many miracle inventions provided by MS to us...)
How to post your question to get the best and quick help




Hall of Fame
Group: General Forum Members
Last Login: Sunday, November 23, 2014 12:50 AM
Points: 3,109,
Visits: 11,515


This should work also:
select abs(sign(col1)+sign(col2)+sign(col3)+ sign(col4)+sign(col5)) as NegativeCount sign(col1)+sign(col2)+sign(col3)+ sign(col4)+sign(col5)+5 as PositiveCount from MyTable




SSCrazy
Group: General Forum Members
Last Login: 2 days ago @ 8:23 AM
Points: 2,873,
Visits: 5,189


Michael Valentine Jones (11/19/2012)
This should work also: select abs(sign(col1)+sign(col2)+sign(col3)+ sign(col4)+sign(col5)) as NegativeCount sign(col1)+sign(col2)+sign(col3)+ sign(col4)+sign(col5)+5 as PositiveCount from MyTable
Have you tested it? You should try:
CREATE TABLE [dbo].[MyTable]( [col1] [int] NOT NULL, [col2] [int] NOT NULL, [col3] [int] NOT NULL, [col4] [int] NOT NULL, [col5] [int] NOT NULL ) ON [PRIMARY]
insert MyTable select 0,0,0,0,0 union select 3,5,15,6,0 union select 3,5,15,6,16 union select 10,14,8,17,16 union select 11,10,5,2,4 union select 6,2,7,13,8 union select 4,2,6,7,19 union select 10,8,7,16,14 union select 17,17,9,14,18 union select 7,19,15,12,13 union select 7,19,15,12,13
It's not so simple as appears at first glance, so check my version...
_____________________________________________ "The only true wisdom is in knowing you know nothing" "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!" (So many miracle inventions provided by MS to us...)
How to post your question to get the best and quick help




SSCrazy
Group: General Forum Members
Last Login: 2 days ago @ 8:23 AM
Points: 2,873,
Visits: 5,189


Actually, I found even more elegant way:
SELECT * FROM tablename CROSS APPLY(SELECT SUM(SIGN(c) + ABS(SIGN(c)))/2 AS Positive ,ABS(SUM(SIGN(c)  ABS(SIGN(c)))/2) AS Negative FROM (VALUES (col1),(col2),(col3),(col4),(col5)) c(c)) calc
... however, I will not be surprised if CASE WHEN outperform all of mathematicalpuzzle based. It requires some testing, but I have no time for this right now
_____________________________________________ "The only true wisdom is in knowing you know nothing" "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!" (So many miracle inventions provided by MS to us...)
How to post your question to get the best and quick help




Hall of Fame
Group: General Forum Members
Last Login: 2 days ago @ 4:53 AM
Points: 3,422,
Visits: 5,368


Eugene Elutin (11/19/2012)
Michael Valentine Jones (11/19/2012)
This should work also: select abs(sign(col1)+sign(col2)+sign(col3)+ sign(col4)+sign(col5)) as NegativeCount sign(col1)+sign(col2)+sign(col3)+ sign(col4)+sign(col5)+5 as PositiveCount from MyTable Have you tested it? You should try: CREATE TABLE [dbo].[MyTable]( [col1] [int] NOT NULL, [col2] [int] NOT NULL, [col3] [int] NOT NULL, [col4] [int] NOT NULL, [col5] [int] NOT NULL ) ON [PRIMARY]
insert MyTable select 0,0,0,0,0 union select 3,5,15,6,0 union select 3,5,15,6,16 union select 10,14,8,17,16 union select 11,10,5,2,4 union select 6,2,7,13,8 union select 4,2,6,7,19 union select 10,8,7,16,14 union select 17,17,9,14,18 union select 7,19,15,12,13 union select 7,19,15,12,13
It's not so simple as appears at first glance, so check my version...
I think you meant to use "union all select" so that the last entry (a duplicate) is retained.
Your next solution gets a +1!
My mantra: No loops! No CURSORs! No RBAR! Hoouh!
My thought question: Have you ever been told that your query runs too fast?
My advice: INDEXing a poorperforming query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it? The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead? Since random numbers are too important to be left to chance, let's generate some! Learn to understand recursive CTEs by example. Splitting strings based on patterns can be fast!




SSCrazy
Group: General Forum Members
Last Login: Yesterday @ 11:39 PM
Points: 2,031,
Visits: 2,533


I tried for 6 numbers with the below query.
select col1, col2, col3, col4, col5, Col6 ,(s1+abs(s1)+s2+abs(s2)+s3+abs(s3)+s4+abs(s4)+s5+abs(s5)+ s6+abs(s6))/2 as Positive ,abs((s1abs(s1)+s2abs(s2)+s3abs(s3)+s4abs(s4)+s5abs(s5)+ s6  abs(s6))/2) as Negative from (select *,sign(col1) s1,sign(col2) s2,sign(col3) s3,sign(col4) s4,sign(col5) s5, sign(Col6) s6 from MyTable) sn
select *, 6Positives as Negatives from (select *,(sign(col1)+sign(col2)+sign(col3)+sign(col4)+sign(col5)+sign(Col6)+ 6)/2 Positives from MyTable) sn It works fine.
what is the secret behind this mathematical formula? It would be great if you explain it in detail.
I am always your fan for such a mathematical formula ( The one you used to generate the sequence number by using BIT WISE & operator)
karthik




SSCrazy
Group: General Forum Members
Last Login: 2 days ago @ 8:23 AM
Points: 2,873,
Visits: 5,189


dwain.c (11/19/2012)
Eugene Elutin (11/19/2012)
[quote] Michael Valentine Jones (11/19/2012)
This should work also: select ... union select 7,19,15,12,13 union select 7,19,15,12,13
It's not so simple as appears at first glance, so check my version... I think you meant to use "union all select" so that the last entry (a duplicate) is retained. Your next solution gets a +1!
No, I really didn't, it was just cut&paste, the data variation above is enough for testing functionality of the query... Thanks for "+1", I like it too
_____________________________________________ "The only true wisdom is in knowing you know nothing" "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!" (So many miracle inventions provided by MS to us...)
How to post your question to get the best and quick help



