May 12, 2008 at 4:05 pm
using sql 2000, I'm trying to create 1 row from a union query. This is needed by a DNN module to create a stacked bar chart to show the actual amount and the total goal.
The sql is:
select '(Total)' as trust,
sum(totalprocessed) as 'Total Goal'
from vw_total_numbers_0 where right(trust,4) = 'goal'
UNION ALL
select '(Total)' as trust,
sum(totalprocessed) as 'Actual'
from vw_total_numbers_0 where right(trust,4) <> 'goal'
and it returns the results in 2 rows:
(Total_goal) 74734
(Total_actual) 25265
However, the DNN module needs this in 1 row: ' Total 74734 25265 ' and will use this to create a stacked bar chart showing both to look like a thermometer-type chart where the 74,734 is the total goal and so far we've done 25,265.
Is there a way to get this into 1 row, and 3 columns? Any help is greatly appreciated, thanks!
May 13, 2008 at 4:21 am
Declare two variable and set the variable to store the result of aggregate column of each select Query
select 'Total', Variable1, Variable2
Hope this is your expected result
Regards,
Rajesh
May 13, 2008 at 4:22 am
SELECT 'Total'
,(SELECT SUM(totalprocessed)
FROM vw_total_numbers_0
WHERE RIGHT(trust,4) = 'goal') AS 'Total Goal'
,(SELECT SUM(totalprocessed)
FROM vw_total_numbers_0
WHERE RIGHT(trust,4) <> 'goal') AS 'Actual'
- Zahran -
May 13, 2008 at 7:33 am
Got it, thanks so much!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply