Return a record depends of group result

• makis_best

  Mr or Mrs. 500

  Points: 528

  More actions

  April 12, 2015 at 4:21 am

  #306962

  Hi

  I have a table with records like that.

  Group | Value

  Team 1 | 0

  Team 1 | 0

  Team 1 | 1

  Team 1 | 1

  Team 2 | 0

  Team 2 | 0

  Team 2 | 0

  I want a script that return 0 if all the values of the group are 0 and return 1 if the records of the value is mixed with 0 and 1.

  Is that possible?

  Please help.

  Thank you.

• Eirikur Eiriksson

  SSC Guru

  Points: 182912

  More actions

  April 12, 2015 at 4:51 am

  #1790384

  makis_best (4/12/2015)

  ---

  Hi

  I have a table with records like that.

  Group | Value

  Team 1 | 0

  Team 1 | 0

  Team 1 | 1

  Team 1 | 1

  Team 2 | 0

  Team 2 | 0

  Team 2 | 0

  I want a script that return 0 if all the values of the group are 0 and return 1 if the records of the value is mixed with 0 and 1.

  Is that possible?

  Please help.

  Thank you.

  Quick suggestion, use sign function on a group by aggregation

  😎

  USE tempdb;

  GO

  SET NOCOUNT ON;

  IF OBJECT_ID(N'dbo.TBL_TEAM_GROUP') IS NOT NULL DROP TABLE dbo.TBL_TEAM_GROUP;

  CREATE TABLE dbo.TBL_TEAM_GROUP

  (

  TG_Group VARCHAR(25) NOT NULL

  ,TG_Value INT NOT NULL

  );

  INSERT INTO dbo.TBL_TEAM_GROUP (TG_Group,TG_Value)

  VALUES

  ('Team 1',0)

  ,('Team 1',0)

  ,('Team 1',1)

  ,('Team 1',1)

  ,('Team 2',0)

  ,('Team 2',0)

  ,('Team 2',0);

  SELECT

  TG.TG_Group

  ,SIGN(SUM(TG.TG_VALUE)) AS GR_FLAG

  FROM dbo.TBL_TEAM_GROUP TG

  GROUP BY TG.TG_Group;

  Results

  TG_Group GR_FLAG

  ---------- --------

  Team 1 1

  Team 2 0