group by & having or ranking

• SJanki

  SSCrazy

  Points: 2847

  More actions

  February 10, 2012 at 8:57 am

  #264367

  In a table I have a column 'PNumber' and a column 'ReasonCode', among other columns.

  eg.

  PNumber ReasonCode

  P1E1

  P1E1

  P1D1

  P1D1

  P1D1

  P2E1

  P3E1

  P3D1

  P3D1

  I want to get only those PNumber , which has both 'E1' & 'D1' , and want to get the count of E1 & D1 in those PNumbers.

  eg.

  P1E12 times

  P1D12 times

  P3E11 time

  P3D12 times

  I tried using ranking, group by and having , but not able to get exactly what I want.

  Returning to TSQL after a while....need some help here!

• murataposta

  Valued Member

  Points: 58

  More actions

  February 10, 2012 at 9:02 am

  #1445637

  SELECT P, E, COUNT(*) FROM TABLE

  GROUP BY P,E

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  February 10, 2012 at 9:09 am

  #1445642

  DECLARE @t TABLE(PNumber VARCHAR(2),ReasonCode VARCHAR(2))

  INSERT INTO @t(PNumber,ReasonCode)

  SELECT 'P1', 'E1' UNION ALL

  SELECT 'P1', 'E1' UNION ALL

  --SELECT 'P1', 'D1' UNION ALL

  SELECT 'P1', 'D1' UNION ALL

  SELECT 'P1', 'D1' UNION ALL

  SELECT 'P2', 'E1' UNION ALL

  SELECT 'P3', 'E1' UNION ALL

  SELECT 'P3', 'D1' UNION ALL

  SELECT 'P3', 'D1';

  WITH CTE AS (

  SELECT PNumber,ReasonCode,

  COUNT(*) AS Times,

  COUNT(*) OVER(PARTITION BY PNumber) AS GrpCount

  FROM @t

  WHERE ReasonCode IN ('E1','D1')

  GROUP BY PNumber,ReasonCode)

  SELECT PNumber,ReasonCode,Times

  FROM CTE

  WHERE GrpCount=2

  ORDER BY PNumber,ReasonCode DESC;

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• SJanki

  SSCrazy

  Points: 2847

  More actions

  February 10, 2012 at 9:28 am

  #1445657

  Mark-101232 (2/10/2012)

  ---

  DECLARE @t TABLE(PNumber VARCHAR(2),ReasonCode VARCHAR(2))

  INSERT INTO @t(PNumber,ReasonCode)

  SELECT 'P1', 'E1' UNION ALL

  SELECT 'P1', 'E1' UNION ALL

  --SELECT 'P1', 'D1' UNION ALL

  SELECT 'P1', 'D1' UNION ALL

  SELECT 'P1', 'D1' UNION ALL

  SELECT 'P2', 'E1' UNION ALL

  SELECT 'P3', 'E1' UNION ALL

  SELECT 'P3', 'D1' UNION ALL

  SELECT 'P3', 'D1';

  WITH CTE AS (

  SELECT PNumber,ReasonCode,

  COUNT(*) AS Times,

  COUNT(*) OVER(PARTITION BY PNumber) AS GrpCount

  FROM @t

  WHERE ReasonCode IN ('E1','D1')

  GROUP BY PNumber,ReasonCode)

  SELECT PNumber,ReasonCode,Times

  FROM CTE

  WHERE GrpCount=2

  ORDER BY PNumber,ReasonCode DESC;

  Thank you so much Mark! This was a quick & helped me submit the results to my boss in time!!

• Jeff Moden

  SSC Guru

  Points: 1004362

  More actions

  February 11, 2012 at 5:05 am

  #1445901

  Shamelessly borrowing from Mark's good code for test data, we can simplify both the code and the results a bit...

  --===== Create the test table/data

  DECLARE @t TABLE(PNumber VARCHAR(2),ReasonCode VARCHAR(2))

  INSERT INTO @t(PNumber,ReasonCode)

  SELECT 'P1', 'E1' UNION ALL

  SELECT 'P1', 'E1' UNION ALL

  SELECT 'P1', 'D1' UNION ALL

  SELECT 'P1', 'D1' UNION ALL

  SELECT 'P2', 'E1' UNION ALL

  SELECT 'P2', 'E1' UNION ALL --Added an extra P2 Row here just to test

  SELECT 'P3', 'E1' UNION ALL

  SELECT 'P3', 'D1' UNION ALL

  SELECT 'P3', 'D1';

  --===== This not only finds the PNumbers that have both reason codes,

  -- it counts how many of each ReasonCode there was on a single row.

  SELECT PNumber,

  D1_Count = SUM(CASE WHEN ReasonCode = 'D1' THEN 1 ELSE 0 END),

  E1_Count = SUM(CASE WHEN ReasonCode = 'E1' THEN 1 ELSE 0 END),

  TotalCount = COUNT(*)

  FROM @t

  WHERE ReasonCode IN ('E1','D1')

  GROUP BY PNumber

  HAVING COUNT(DISTINCT ReasonCode) = 2

  ;

  That returns the following...

  PNumber D1_Count E1_Count TotalCount

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

  P1 2 2 4

  P3 2 1 3

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• SJanki

  SSCrazy

  Points: 2847

  More actions

  February 12, 2012 at 7:05 pm

  #1446084

  Thanks Jeff. This is useful.

• Jeff Moden

  SSC Guru

  Points: 1004362

  More actions

  February 12, 2012 at 10:37 pm

  #1446093

  You bet. Thank you for the feedback.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)