November 19, 2012 at 9:12 am
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
-151295-7
-35-156-16
10-14817-16
-11-1052-4
-6-27-138
-42-6719
-108716-14
17-17-91418
7191512-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
November 19, 2012 at 9:25 am
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
November 19, 2012 at 9:27 am
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
November 19, 2012 at 10:17 am
There is another, more mathematical way:
If no zero values in your columns:
select *, 5-Positives 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((s1-abs(s1)+s2-abs(s2)+s3-abs(s3)+s4-abs(s4)+s5-abs(s5))/2) as Negative
from (select *,sign(col1) s1,sign(col2) s2,sign(col3) s3,sign(col4) s4,sign(col5) s5
from tablename) sn
November 19, 2012 at 10:20 am
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
November 19, 2012 at 10:29 am
Michael Valentine Jones (11/19/2012)
This should work also:
selectabs(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...;-)
November 19, 2012 at 10:45 am
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 mathematical-puzzle based.
It requires some testing, but I have no time for this right now :Whistling:
November 19, 2012 at 6:02 pm
Eugene Elutin (11/19/2012)
Michael Valentine Jones (11/19/2012)
This should work also:
selectabs(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 thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 19, 2012 at 9:56 pm
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((s1-abs(s1)+s2-abs(s2)+s3-abs(s3)+s4-abs(s4)+s5-abs(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 *, 6-Positives 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
November 20, 2012 at 3:02 am
dwain.c (11/19/2012)
Eugene Elutin (11/19/2012)
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 🙂
November 20, 2012 at 3:39 am
karthik M (11/19/2012)
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((s1-abs(s1)+s2-abs(s2)+s3-abs(s3)+s4-abs(s4)+s5-abs(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 *, 6-Positives as Negativesfrom (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)
It's quite simple there.
SIGN returns -1 for negative, 1 for positive and 0 for zero.
So, in case if we have no 0 (zeros) in data, we only need to find count of positive numbers, as negative count = count of columns - count of positive
As, SIGN returns negative and positive 1's, we just need to "neutralise" negative 1's and sum positive ones. What you can do to achieve it is: N + 1, where N is result of SIGN and when it's negative 1 (-1), result of the "N + 1" will be zero! However we have positive ones as well, and the above calculation will make it 2. So, we divide result of the formula by 2:
(N + 1)/2 and get exactly what we need: It will still return 0 for -1's and 1's will stay as 1's, so we can just sum it.
With a bit more algebra we got the following:
(C1 + 1)/2 + (C2+1)/2 + (C3+1)/2 + (C4+1)/2 + (C5+1)/2 =
(C1 + C2 + C3 + C4 + C5 + 5)/2
Now, where we have 0's in data, it became a bit more complicated, as 0's shouldn't be counted as positive or negative.
So, (N + 1)/2 does still work for counting positive (as in SQL (0+1)/2 will return 0), however algebraic transformation will no longer apply as "+5" bit will screw results. So, we need to find the way to make "1" in the "(N+1)/2)" formula to be also "neutralised" whenever N = 0. Hence use of ABS(N) instead of constant "1". You can see that ABS(-1 or 1) will still be 1 and ABS(0) will turn to 0. Actually, you may notice that for counting positive numbers, it's not really required as long formula is not simplified form it's full form of
(C1 + 1)/2 + (C2+1)/2 + (C3+1)/2 + (C4+1)/2 + (C5+1)/2
As (0 + 1)/2 = 0 in SQL, positive numbers count will be calculated correctly anyway.
However, count of negative numbers is no longer equal to count of columns minus count of positive. It's need to be calculated with full ignorance of 0's.
So we need dedicated formula to count negatives. So, we will try to apply same principals as for calculating positive's count, except now we need to "neutralise" positive ones (1) and sum negative ones (-1). To neutralise positive 1 we need subtract 1 from it: N - 1 will make it work for positives. Now, where N is negative 1 it will make it "-2", so we do the same as in our first case - divide it by 2, so we end up with (-1). The rest is the same: we sum all of -1's and take it absolute value (so negative count will turn to nice positive count ;-)).
Now, while I was explaining all of the above, I have realised, that use of ABS(N) is not really required at all! It was only used to make sure that algebraic transformation (simplification) will not be performed, but if it's not performed explicitly, SQL is not going to do it as well, so formulas can stay in their original forms as:
Count Of Positives = (C1 + 1)/2 + (C2+1)/2 + (C3+1)/2 + (C4+1)/2 + (C5+1)/2
Count Of Negatives = (C1 - 1)/2 + (C2-1)/2 + (C3-1)/2 + (C4-1)/2 + (C5-1)/2
Zero's will be "neutralised" implicitly as long we play only with INT datatypes.
So my final version, with CROSS APPLY, will be even simpler:
SELECT *
FROM tablename
CROSS APPLY(SELECT SUM((SIGN(c) + 1)/2) AS Positive
,ABS(SUM((SIGN(c) - 1)/2)) AS Negative
FROM (VALUES (col1),(col2),(col3),(col4),(col5)) c(c)) calc
November 20, 2012 at 5:19 am
EE,
Great! Thanks a lot for your time and detailed explanation!
karthik
November 20, 2012 at 5:21 am
Since I never some across this kind of situation, I just know what is what.
I have just read about sign() & abs() functions. Thats it.
But I am seeing the real time usage in this example. Give me some more food like this in another thread.
🙂
karthik
November 20, 2012 at 4:47 pm
Thanks everyone for the help.
“If your actions inspire others to dream more, learn more, do more and become more, you are a leader.” -- John Quincy Adams
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply