Selecting a count based upon maximum in a data field

• robin.slater

  SSC Rookie

  Points: 29

  More actions

  November 14, 2012 at 6:55 am

  #284831

  I have customers with various amounts assigned to them by a code. A count is required by the code based upon the maximum of any one amount

  Difficulty is if a customer has more than one amount of the same value

  Data

  ref_idcus_idcodeamt

  ref001cus001C15

  ref002cus001C25

  ref003cus001I25

  ref004cus002C10

  ref005cus002C15

  ref006cus002I20

  Result should be either

  codecount

  C 1

  I1

  or

  C0

  I2

  cus002 has a max amt of 20 therefore code for cus002 is I

  cus001 has a max amt of 25 on both code I and C - so the count for cus001 can goto either C or I but not both

  SQL code used

  create table z_temp (

  ref_id Varchar(6),

  cus_id Varchar(6),

  code Varchar(1),

  amt Money)

  INSERT INTO z_temp ( ref_id, cus_id, code, amt ) values ('ref001', 'cus001', 'C', '15')

  INSERT INTO z_temp ( ref_id, cus_id, code, amt ) values ('ref002', 'cus001', 'C', '25')

  INSERT INTO z_temp ( ref_id, cus_id, code, amt ) values ('ref003', 'cus001', 'I', '25')

  INSERT INTO z_temp ( ref_id, cus_id, code, amt ) values ('ref004', 'cus002', 'C', '10')

  INSERT INTO z_temp ( ref_id, cus_id, code, amt ) values ('ref005', 'cus002', 'C', '15')

  INSERT INTO z_temp ( ref_id, cus_id, code, amt ) values ('ref006', 'cus002', 'I', '20')

  SELECT Code, Count(Distinct [cus_id]) FROM z_temp

  INNER JOIN (SELECT [cus_id] As [max_cus_id], MAX([amt]) as max_amt

  FROM z_temp

  Group BY cus_id) z_temp_max

  ON z_temp.cus_id = z_temp_max.max_cus_id And z_temp.amt = z_temp_max.max_amt

  Group By code

  gives

  Code(No column name)

  C1

  I2

  Any help greatly appreciated

• PB_BI

  SSCoach

  Points: 17463

  More actions

  November 14, 2012 at 7:09 am

  #1558906

  If you don't mind which code it is then something like

  WITH Cte AS

  (SELECT ref_id,

  cus_id,

  code,

  amt,

  ROW_NUMBER() OVER(PARTITION BY cus_id ORDER BY amt DESC) AS Rank

  FROM

  z_temp)

  SELECT Code, count(*) FROM cte WHERE Rank = 1 GROUP BY Code

  should do it.

  ---

  I'm on LinkedIn

• robin.slater

  SSC Rookie

  Points: 29

  More actions

  November 14, 2012 at 8:10 am

  #1558948

  Many thanks, works a treat (I'm not very familiar with OVER(PARTITION ...)

• PB_BI

  SSCoach

  Points: 17463

  More actions

  November 14, 2012 at 8:15 am

  #1558954

  No problem, happy to help.

  Ranking functions are described here: http://msdn.microsoft.com/en-us/library/ms189798.aspx

  They're quite handy 🙂

  ---

  I'm on LinkedIn

• robin.slater

  SSC Rookie

  Points: 29

  More actions

  November 29, 2012 at 5:34 am

  #1563695

  A further question, I am now using the following piece of code to report a count where the code is I

  SELECT count(*)

  FROM (SELECT ref_id, cus_id, code, amt,

  ROW_NUMBER() OVER(PARTITION BY cus_id ORDER BY amt DESC) AS Rank

  FROM z_temp) AS z_temp_max WHERE Rank = 1 And code = 'I'

  which gives the correct result of 1

  part of the statement which gives the rank and amount is

  SELECT ref_id, cus_id, code, amt,

  ROW_NUMBER() OVER(PARTITION BY cus_id ORDER BY amt DESC) AS Rank

  FROM z_temp

  which gives

  ref_idcus_idcodeamtRank

  ref002cus001C25.001

  ref003cus001I25.002

  ref001cus001C15.003

  ref006cus002I20.001

  ref005cus002C15.002

  ref004cus002C10.003

  Logic is cus002 has 3 values, the highest value is 20, which therefore has a rank of 1

  So if code I is selected there is a count of 1

  I now also want to SUM the values and report a value of 45 against a code of I

  SO which ever code has the highest ranked value - report the sum of the values