t-sql

• mtz676

  SSCrazy Eights

  Points: 9233

  More actions

  February 13, 2012 at 4:30 pm

  #252861

  I have a simple select staement which displays result as follows:

  col1 col2 purchaseno saleno col5 col6 col6 FC Total

  A B 123 12345 ABC-1 XXX 10 100 23

  A B 123 12345 ABC-1 XXX 10 100 323

  A B 123 12345 ABC-1 XXX 10 100 2343

  A B 456 72345 ABC-D XXX 10 20 253

  A B 456 72345 ABC-D XXX 10 20 2673

  A B 456 72345 ABC-D XXX 10 20 283

  A B 456 72345 ABC-D XXX 10 20 213

  A B 567 79797 GED-D XXX 36 45 4645

  If there are 3 rows with same purchaseno or saleno with same value then FC should be displayed

  as 100/3=33.3 for the first 3 rows

  and 20/4=5 next 4 rows

  and 45/1 for the last row.

  the result should be as follows:

  col1 col2 purchaseno saleno col5 col6 col6 FC Total

  A B 123 12345 ABC-1 XXX 10 33.3 23

  A B 123 12345 ABC-1 XXX 10 33.3 323

  A B 123 12345 ABC-1 XXX 10 33.3 2343

  A B 456 72345 ABC-D XXX 10 5 253

  A B 456 72345 ABC-D XXX 10 5 2673

  A B 456 72345 ABC-D XXX 10 5 283

  A B 456 72345 ABC-D XXX 10 5 213

  A B 567 79797 GED-D XXX 36 45 4645

  How do I do this.

  Thanks

• Jeff Moden

  SSC Guru

  Points: 1004475

  More actions

  February 13, 2012 at 8:22 pm

  #1446606

  In the future, please post your data using the methods found in the article at the first link in my signature line below. 😉

  Also, your example data has two "col6" so I took my liberties with naming it. Here's how you should post your example data in the future complete with a table definition.

  CREATE TABLE #YourTable

  (

  col1 CHAR(1),

  col2 CHAR(1),

  purchaseno INT,

  saleno INT,

  col5 CHAR(5),

  col6 CHAR(3),

  col6a INT,

  FC INT,

  Total INT

  )

  ;

  INSERT INTO #YourTable

  (col1,col2,purchaseno,saleno,col5,col6,col6a,FC,Total)

  SELECT 'A','B','123','12345','ABC-1','XXX','10','100','23' UNION ALL

  SELECT 'A','B','123','12345','ABC-1','XXX','10','100','323' UNION ALL

  SELECT 'A','B','123','12345','ABC-1','XXX','10','100','2343' UNION ALL

  SELECT 'A','B','456','72345','ABC-D','XXX','10','20','253' UNION ALL

  SELECT 'A','B','456','72345','ABC-D','XXX','10','20','2673' UNION ALL

  SELECT 'A','B','456','72345','ABC-D','XXX','10','20','283' UNION ALL

  SELECT 'A','B','456','72345','ABC-D','XXX','10','20','213' UNION ALL

  SELECT 'A','B','567','79797','GED-D','XXX','36','45','4645'

  ;

  Here's the answer to your problem.

  SELECT col1,col2,purchaseno,saleno,col5,col6,col6a,

  FC = (FC+0.0)/COUNT(*) OVER (PARTITION BY col1,col2,purchaseno,saleno,col5,col6,col6a),

  Total

  FROM #YourTable

  ;

  Here are the results:

  col1 col2 purchaseno saleno col5 col6 col6a FC Total

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

  A B 123 12345 ABC-1 XXX 10 33.333333333333 23

  A B 123 12345 ABC-1 XXX 10 33.333333333333 323

  A B 123 12345 ABC-1 XXX 10 33.333333333333 2343

  A B 456 72345 ABC-D XXX 10 5.000000000000 253

  A B 456 72345 ABC-D XXX 10 5.000000000000 2673

  A B 456 72345 ABC-D XXX 10 5.000000000000 283

  A B 456 72345 ABC-D XXX 10 5.000000000000 213

  A B 567 79797 GED-D XXX 36 45.000000000000 4645

  (8 row(s) affected)

  --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)