Using MAX and COUNT function to display MAX(COUNT) row(s) from a table

• solig.fi

  SSC Journeyman

  Points: 90

  More actions

  October 8, 2010 at 12:22 am

  #224829

  Hi,

  I made this query which counts the number of times a particular value is used for a customer. i.e. Customer 1000 can have 5 values, i would like to display the customer, value and the most commonly used value.

  the sql query i wrote;

  select

  kunnr,

  zterm,

  count(zterm) as cnt

  from customers

  where kunnr in ('1001','1020','1234','2520','1007')

  group by kunnr,zterm

  the result i get;

  kunnrztermcnt

  1001W2001

  1001W3003

  1001ZPRE2

  1007 1

  1007W3002

  1007W9011

  1007ZPRE2

  1020W2003

  1020W3003

  1020W3021

  1020W6001

  1234W2001

  2520W2001

  2520W3002

  2520W4501

  2520W6001

  I tried to used the MAX function on top of this query, but i dont get the desired results

  SELECT kunnr, zterm, cnt

  FROM customers

  where cnt =

  (select max(cnt) from customers)

  the result;

  1020W3003

  1001W3003

  1020W2003

  What the end result should be;

  kunnrztermcnt

  1001W3003

  1007W3002

  1007ZPRE2

  1020W2003

  1020W3003

  1234W2001

  2520W3002

  I have searched the forum and google but still no clear answers as to what i can do to fix this, thanks for the help!

  solig

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  October 8, 2010 at 2:20 am

  #1234059

  Try this :

  DECLARE @tab TABLE

  (

  kunnr INT,

  zterm VARCHAR(15),

  cnt INT

  )

  INSERT INTO @tab ( kunnr , zterm , cnt )

  SELECT 1001,'W200',1

  UNION ALL SELECT 1001,'W300',3

  UNION ALL SELECT 1001,'ZPRE',2

  UNION ALL SELECT 1007,'W200',1

  UNION ALL SELECT 1007,'W300',2

  UNION ALL SELECT 1007,'W901',1

  UNION ALL SELECT 1007,'ZPRE',2

  UNION ALL SELECT 1020,'W200',3

  UNION ALL SELECT 1020,'W300',3

  UNION ALL SELECT 1020,'W302',1

  UNION ALL SELECT 1020,'W600',1

  UNION ALL SELECT 1234,'W200',1

  UNION ALL SELECT 2520,'W200',1

  UNION ALL SELECT 2520,'W300',2

  UNION ALL SELECT 2520,'W450',1

  UNION ALL SELECT 2520,'W600',1

  ; WITH CTE0 AS

  (

  SELECT RN = ROW_NUMBER() OVER (PARTITION BY kunnr ORDER BY cnt DESC) ,

  kunnr , zterm , cnt

  FROM @tab

  )

  SELECT kunnr , zterm , cnt

  FROM CTE0

  WHERE RN = 1

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  October 8, 2010 at 2:29 am

  #1234065

  Or this, which works on the raw data i.e. before the first aggregation step:

  SELECT

  d.kunnr,

  d.zterm,

  d.cnt

  FROM (

  SELECT

  g.kunnr,

  g.zterm,

  g.cnt,

  rn = ROW_NUMBER() OVER (PARTITION BY kunnr ORDER BY zterm, cnt DESC)

  FROM (

  SELECT

  kunnr,

  zterm,

  cnt = COUNT(*)

  FROM customers

  WHERE kunnr in ('1001','1020','1234','2520','1007')

  GROUP BY kunnr, zterm

  ) g

  ) d

  WHERE d.rn = 1

  Note the use of derived tables rather than CTE's because it's friday.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  October 8, 2010 at 3:07 am

  #1234083

  Chris Morris-439714 (10/8/2010)

  ---

  Note the use of derived tables rather than CTE's because it's friday.

  rofl :w00t:

• solig.fi

  SSC Journeyman

  Points: 90

  More actions

  October 8, 2010 at 3:43 am

  #1234098

  Thanks ColdCoffee and Chris! both of solutions seems to work, although i have not used this PARTITION BY function before so i will try to study & understand what is really happening in both of the solutions you have provided!

  solig