August 5, 2009 at 10:25 am
I have a stored procedure for a report that looks something like this
select
nameofmarket as "market",
sum(sales) as sales,
sum(price) as cost,
avg(sales) as avg sale,
goods,
ordersplaced
from dbo.trading
union
select
nameofmarket as "market",
sum(sales) as sales,
sum(price) as cost,
avg(sales) as avg sale,
goods,
ordersplaced
from dbo.selling
it returns something like this
market | Sales| cost|avg sale|ordersplaced
canberra |1,000|300|25|40
sydney | 2,000|800|40|50
wellington|6,000|1200|120|50
auckland|8,000 |1200|20|400
I want to include a total at the bottom so I have something like this
market | Sales| cost|avg sale|ordersplaced
canberra |1,000|300|25|40
sydney | 2,000|800|40|50
wellington|6,000|1200|120|50
auckland|8,000 |1200|20|400
total|17,000|3,500 |avg of the avg|540
how do i tweak the stored procedure to achieve this?
August 5, 2009 at 10:37 am
Try GROUP BY WITH ROLLUP
declare @trading table (
nameofmarket varchar(20),
sales decimal(18,6),
price decimal(18,6),
goods int,
ordersplaced int
)
insert into @trading
select 'canberra' ,1000,300,25 ,40 union all select
'sydney' , 2000,800,40 ,50 union all select
'wellington',6000,1200,120 ,50 union all select
'auckland',8000 ,1200,20 ,400
select
nameofmarket as "market",
sum(sales) as sales,
sum(price) as cost,
avg(sales) as [avg sale],
sum(ordersplaced) as ordersplaced
from @trading
group by nameofmarket with rollup
Regards
Gianluca
-- Gianluca Sartori
August 5, 2009 at 10:40 am
In SQL2008 you can use the new ISO syntax to perform rollups, as old WITH ROLLUP syntax will be removed in future versions:
select
nameofmarket as "market",
sum(sales) as sales,
sum(price) as cost,
avg(sales) as [avg sale],
sum(ordersplaced) as ordersplaced
from @trading
group by rollup(nameofmarket)
-- Gianluca Sartori
August 5, 2009 at 10:41 am
You'll need to add a WITH ROLLUP to a GROUP BY
///Edit - sorry, excuse me, Little Sir Echo
August 5, 2009 at 11:02 am
Thanks a lot guys for the responses. Ill try it out and provide feedback. Thanks again !!
August 5, 2009 at 12:02 pm
I think you missed the average sale part, I need to get the avg of the sum of averages (if that makes any sense).
Like if averages are 10, 20, 30, i need an average of that i.e avg of sum of 10,20,30 for my avg column. Other than that, the rollup works really well
August 5, 2009 at 12:59 pm
I am sure this is not optimal and I have no clue what performance this would have with a large data set but here is what I did:
declare @trading table (
nameofmarket varchar(20),
sales decimal(18,6),
price decimal(18,6),
goods int,
ordersplaced int
)
insert into @trading
select
'canberra',1000,300,25.0,40 union all select
'sydney',2000,800,40.0,50 union all select
'wellington',6000,1200 ,120.0 ,50 union all select
'auckland',8000,1200,20.0,40 UNION ALL SELECT
'canberra',500,100,75.0,60
;WITH CTE AS
(
select
nameofmarket as "market",
sum(sales) as sales,
sum(price) as cost,
avg(sales) as [avg sale],
sum(ordersplaced) as ordersplaced
from @trading
GROUP BY nameofmarket
)
SELECT ISNULL(CTE.market,'TOTAL'), sum(CTE.sales), sum(CTE.cost), avg(CTE.[avg sale]), sum(CTE.ordersplaced)
FROM CTE
GROUP BY rollup(market)
//EDIT
might need to change to this if the market column allows nulls
SELECT CASE GROUPING(CTE.market) WHEN 1 THEN 'TOTAL' ELSE CTE.market end market,
sum(CTE.sales) sales,
sum(CTE.cost) price,
avg(CTE.[avg sale]) [avg sale],
sum(CTE.ordersplaced) ordersplaced
FROM CTE
GROUP BY rollup(market)
August 5, 2009 at 1:37 pm
The CTE was not a recognizable grouping option so I replaced it with a temp table and it gave me the result I was looking for. Thanks for all your help guys. I really appreciate it.
August 5, 2009 at 11:05 pm
Gianluca Sartori (8/5/2009)
In SQL2008 you can use the new ISO syntax to perform rollups, as old WITH ROLLUP syntax will be removed in future versions:
select
nameofmarket as "market",
sum(sales) as sales,
sum(price) as cost,
avg(sales) as [avg sale],
sum(ordersplaced) as ordersplaced
from @trading
group by rollup(nameofmarket)
Like I said... "rapidly becoming one of the greats". 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
August 6, 2009 at 3:27 am
Jeff Moden (8/5/2009)
Like I said... "rapidly becoming one of the greats". 😉
Thanks Jeff, I look forward the day I deserve your kindness! 😉
-- Gianluca Sartori
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply