Senin, 08 Agustus 2011
Roll Up & Cube
Berikut sampel penggunaan Roll Up dan Cube untuk menganalisis data:
select customername, sum(salesvalue) sales, vsn
from invoices a
inner join customers b on (a.customerid=b.customerid)
inner join products c on (a.vsnid=c.vsnid)
where year(edate)=2011 and month(edate)=8
group by customername, vsn
select
case when grouping(customername)=1 then 'All Customer'
else customername
end customername,
case when grouping(vsn)=1 then 'All product'
else vsn
end vsn, sum(salesvalue)
from invoices a
inner join customers b on (a.customerid=b.customerid)
inner join products c on (a.vsnid=c.vsnid)
where year(edate)=2011 and month(edate)=8
group by customername, vsn
with rollup
select
case when grouping(customername)=1 then 'All Customer'
else customername
end customername,
case when grouping(vsn)=1 then 'All product'
else vsn
end vsn, sum(salesvalue)
from invoices a
inner join customers b on (a.customerid=b.customerid)
inner join products c on (a.vsnid=c.vsnid)
where year(edate)=2011 and month(edate)=8
group by customername, vsn
with cube
Langganan:
Posting Komentar (Atom)
0 comments:
Posting Komentar