Question about Matrix (maybe??)

• meridethvo

  SSC Rookie

  Points: 27

  More actions

  November 10, 2016 at 11:38 am

  #331620

  Hi there...

  I have a dataset in my report that returns the following information:

  Client ID Appointment Date

  1 x

  1 y

  2 c

  3 y

  3 c

  4 a

  4 b

  4 c

  I would like my report to show me an output that looks like:

  # of Appointments # of Clients

  1 1

  2 2

  3 1

  I am just not sure how to get the first column to group by the number of appointments??

  Appreciate the help!

• Chris Harshman

  SSC-Forever

  Points: 42192

  More actions

  November 10, 2016 at 12:06 pm

  #1911751

  simplest way to write it is have a subquery to determine the count of appointments per client, then group again by that count:

  CREATE TABLE #appt (ClientID int, AppointmentDate char(1))

  INSERT INTO #appt

  VALUES

  (1,'x'),

  (1,'y'),

  (2,'c'),

  (3,'y'),

  (3,'c'),

  (4,'a'),

  (4,'b'),

  (4,'c')

  SELECT ApptCount, COUNT(*) AS ClientCount

  FROM (SELECT COUNT(*) AS ApptCount FROM #appt GROUP BY ClientID) ac

  GROUP BY ac.ApptCount