Quick T-SQL Help Please

[Pho3nix]

Hi guys,

Haven't played with SQL in a minute and stuck on some logic.
[table="width: 500"]
[tr]
[td]Id[/td]
[td]Name[/td]
[td]Type[/td]
[/tr]
[tr]
[td]1[/td]
[td]Bob[/td]
[td]Positive[/td]
[/tr]
[tr]
[td]2[/td]
[td]Bob[/td]
[td]Positive[/td]
[/tr]
[tr]
[td]3[/td]
[td]Bob[/td]
[td]Negative[/td]
[/tr]
[/table]

I have the above table and I'm looking to set the type to Positive. How would I do this?
output :

Bob

Positive

Code is actually a little more complex and trying to explain as best I can

EDIT : The output above with the three bobs is a result set from another query. I need to count the positives and negatives from that result set and only return the one that comes up the most.

 

[Baxteen]

up[B]da[/B]te [table_name]
s[B]e[/B]t type = 'positive'
w[B]her[/B]e id = 3

 

[Baxteen]

fml that was difficuilt to post, as giving the answer gets you banned from the site for attempt at sql injection attacks.

 

[vic777]

fml that was difficuilt to post, as giving the answer gets you banned from the site for attempt at sql injection attacks.

I got the same, haha

 

[skimread]

Someone found the cure for AIDS

 

[Urist]

declare @cmd nvarchar(1000)
set @cmd = reverse('''boB'' = eman erehw ''evitisop'' = epyt tes elbat etadpu');

exec (@cmd)

 

[Baxteen]

declare @cmd nvarchar(1000)
set @cmd = reverse('''boB'' = eman erehw ''evitisop'' = epyt tes elbat etadpu');

exec (@cmd)

well done dude
that is a lot of effort.

 

[Baxteen]

declare @cmd nvarchar(1000)
set @cmd = reverse('''boB'' = eman erehw ''evitisop'' = epyt tes elbat etadpu');

exec (@cmd)

side note, based on the table above, it will make all Bob's positive. just a thought. rather update using the pK

 

[Pho3nix]

Omg lol I meant from a select, as a grouping of sorts.

 

[Urist]

side note, based on the table above, it will make all Bob's positive. just a thought. rather update using the pK

what is injection for, if not for mischief , though, he said he wanted bob positive

 

[Pho3nix]

In a meeting and away from my laptop now but will give a proper example now

 

[Urist]

Omg lol I meant from a select, as a grouping of sorts.

oh, lol, imo easiest is to make a common table expression, and then update that, though its not standard.

with x as (select statement) update x ....

 

[Nerfherder]

side note, based on the table above, it will make all Bob's positive. just a thought. rather update using the pK

From the OP he wants all bob's updated

This is probably the real reason he is posting the question though

 

[Pho3nix]

From the OP he wants all bob's updated

This is probably the real reason he is posting the question though

I see how the initial post was worded and how it sounds that way. :| Will update

oh, lol, imo easiest is to make a common table expression, and then update that, though its not standard.

with x as (select statement) update x ....

The output above with the three bobs is a result set from another query. I need to count the positives and negatives from that result set and only return the one that comes up the most.

 

[Baxteen]

case statement.
in your select.
sum( case
when type = "positive" then 1
ELSE 0) as poscount,

sum( case
when type = "negative" then 1
ELSE 0) as negcount,
from blah blah

that will return the counts as 2 fields.

now select from that result set the greater number between the two.

 

[Urist]

Asuming your table name is results

select x.name, x.positives, x.negatives,
case when x.Positives > x.Negatives then 'Positive' else 'Negative' end as Final
from
(
select distinct r.name, 
(select count(*) from results r2 where r2.name = r.name and r2.type = 'Positive') as Positives,
(select count(*) from results r2 where r2.name = r.name and r2.type ='Negative') as Negatives
from results r
) x

pardon the lack of formatting

 

[envo]

You guys should crack a T-SQL book once in a while

select Name, Type from [YourTable] 
group by Name,Type
Having count(case when Type = 'Positive' then 1 end) > count(case when Type='Negative' then 1 end)

 

[envo]

Asuming your table name is results

select x.name, x.positives, x.negatives,
case when x.Positives > x.Negatives then 'Positive' else 'Negative' end as Final
from
(
select distinct r.name, 
(select count(*) from results r2 where r2.name = r.name and r2.type = 'Positive') as Positives,
(select count(*) from results r2 where r2.name = r.name and r2.type ='Negative') as Negatives
from results r
) x

pardon the lack of formatting

Way too complicated and unnecessary when you could just using Group By/Having

I noticed this with a lot of guys learning about nested table selects that they all of a sudden use it everywhere. At scale, it will suck balls trying to beg the server gods for forgiveness in your long-running query causing issues

 

[Pho3nix]

case statement.
in your select.
sum( case
when type = "positive" then 1
ELSE 0) as poscount,

sum( case
when type = "negative" then 1
ELSE 0) as negcount,
from blah blah

that will return the counts as 2 fields.

now select from that result set the greater number between the two.

doesn't work

 

[Pho3nix]

blah blah blah outside query

(SELECT 
						CASE 
							WHEN SUM(c.PosCount) > SUM(c.PosCount) THEN 'Positive'
							WHEN SUM(c.PosCount) < SUM(c.PosCount) THEN 'Negative'
						END AS Type
					 FROM calculate c 
					 WHERE c.Id = so.Id
)