HI,
I have used a pivot query but I'm not sure how to use rollup in the query to get the total at the bottom of the results.
Table is similar to below.
date | product | organisation | price
1 july 2012 | biscuitA | xyz | 33
1 july 2012 | icecreamA | abc | 45
1 july 2012 | biscuitB | abc | 22
1 july 2012 | biscuitA | def | 33
1 july 2012 | biscuitB | xyz | 22
2 july 2012 | biscuitA | def | 33
2 july 2012 | icecreamA | xyz | 45
2 july 2012 | icecreamA | abc | 45
2 july 2012 | biscuitB | abc | 22
3 july 2012 | biscuitA | def | 33
3 july 2012 | icecreamA | xyz | 45
3 july 2012 | biscuitA | xyz | 33
My code is below.
select product, [xyz] as xyz, [abc] as abc, [def] as def, sum([xyz] + [abc] + [def] as Total
from
(
select date, product, price, organisation from table1
) as t
pivot
(
sum(price) for organisation in ([xyz], [abc], [def])
)
as newquery
group by product, abc, xyz, def
The query works fine but now I want to display total at the bottom of the results. I tried 'with rollup' in the last line of my code after group by but it does not produce result as I wanted.
how to use rollup in a query with pivot clause
Posted - 08/20/2012 : 16:18:06
