Union Views

• dba_user

  Default port

  Points: 1458

  More actions

  September 23, 2004 at 8:36 am

  #59615

  I think I already know the answer to this, but I thought I'd take a shot and post a question in case anyone knows a work-around with this:

  We have a view that combines three different types of information: It combines a players stats, totals, and averages so that when the view is queried it looks like this:

  Player       Pts         Rebounds

  Player A    10.000          8.000

  Player B     10.000         10.000

  Total         20.000         18.000

  Average     10.000          9.000

  The view combines the information in three sections: Ord 1 which is the Playier information and Ord 2 which is the Total Information and Ord 3 which is the Average information - a skeletal creation of the view is as follows

  select 1 as ord, player as player, sum(pts), sum(rebs) from sum_players where tm = #tm#

  union

  select 2 as ord, 'Total' as player, sum(pts), sum(rebs) from sum_team where tm = #tm#

  union

  select 3 as ord, 'Average' as player, avg(pts), avg(rebs) from sum_team where tm = #tm#

  THe issue is that people want the Average to be displayed with a precision of 3 decimal points, but the Totals and Player stats in whole numbers.  So it looks like this:

  Player       Pts         Rebounds

  Player A    10               8

  Player B     10              10

  Total         20            18

  Average     10.000          9.000

  I know that the fields have to be the same DataType, but does anyone know a way I can work around this to make the Output appear differently??

  Thanks in advance!!

• AJ Ahrens

  SSC-Insane

  Points: 20676

  More actions

  September 23, 2004 at 10:22 am

  #524210

  Try researching:

  AVG(CAST(pts AS NUMERIC(10, 3)))

  You may have to also RECAST the AVG but this should help you along

  
  
  Good Hunting!

  AJ Ahrens
  
  
  webmaster@kritter.net

• Aaron Templeton

  SSCarpal Tunnel

  Points: 4655

  More actions

  September 23, 2004 at 12:39 pm

  #524243

  You can convert them to varchar.

  select 1 as ord, player as player, CONVERT(varchar, sum(pts)), CONVERT(varchar, sum(rebs)) from sum_players where tm = #tm#

  union

  select 2 as ord, 'Total' as player, CONVERT(varchar, sum(pts)), CONVERT(varchar, sum(rebs)) from sum_team where tm = #tm#

  union

  select 3 as ord, 'Average' as player, CONVERT(varchar, CONVERT(int, avg(pts))), CONVERT(varchar, CONVERT(int, avg(rebs))) from sum_team where tm = #tm#