Aggregate query help

• Dustin W. Jones

  Ten Centuries

  Points: 1207

  More actions

  April 19, 2010 at 8:08 am

  #103926

  select opr_id,evt_id, count(*)

  from dbo.rxrep_rept_card with(nolock)

  where acty_dte > '03/06/2010' and acty_dte < '04/06/2010' and evt_id in(12000,12200,12300,12500)

  group by opr_id, evt_id

  order by opr_id

  The above query returns data like:

  Opr_id evt_id (no column name)

  aaaaaa 12000 10

  bbbbbb 12200 20

  cccccc 12000 50

  dddddd 12200 50

  I want it to return an additional column of data that is calculated as (from the example above) 10 / (10 + 20) so the value in the column in this example would be = “.333…”

  The calculations are based on the 2 different evt_id types for the each specific user

  So it could look something like:

  Opr_id evt_id (no column name) column a

  PRGTDK 12000 10 .333

  PRGTDK 12200 20 .667

  PRGJC1 12000 1 .25

  PRGJC1 12200 3 .75

  Dustin W. Jones - Database Tech.

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  April 19, 2010 at 10:04 am

  #1153945

  I don't understand where your "10 / (10 + 20)" comes from.

  -- Gianluca Sartori