September 23, 2004 at 8:36 am
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!!
September 24, 2004 at 7:07 am
Seems like a presentation problem more than a sql one but you are right the union output has to have the same dataype, so your best bet it to use char output like this
select 1 as ord, player as player, str(sum(pts),8,0), str(sum(rebs),8,0) from sum_players where tm = #tm#
union
select 2 as ord, 'Total' as player, str(sum(pts),8,0), str(sum(rebs),8,0) from sum_team where tm = #tm#
union
select 3 as ord, 'Average' as player, str(avg(pts),8,3), str(avg(rebs),8,3) from sum_team where tm = #tm#
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply