Nu sunt familiar cu clauza ROLLUP din Oracle, dar cred că poţi să obţii rezultatul dorit folosind funcţia GROUPING pentru a filtra rândurile cu subtotaluri, de exemplu:
select continent As Continet, countryregioncode As Cod, name as Tara,sum(saleslastyear) As Vanzari
from SalesTerritory group by name, continent,countryregioncode with rollup
HAVING GROUPING(name) = GROUPING(continent)
Folosind un alt exemplu din AdventureWorks, să presupunem că avem următorul view:
CREATE VIEW Purchasing.PurchasesByVendorsAndStates
AS
SELECT V.Name AS VendorName, A.City, s.Name AS StateProvinceName, CountryRegionCode, P.TotalDue
FROM Purchasing.PurchaseOrderHeader P INNER JOIN Purchasing.Vendor V ON P.VendorID = V.VendorID
INNER JOIN Purchasing.VendorAddress VA ON V.VendorID = VA.VendorID
INNER JOIN Person.Address AS A ON VA.AddressID=A.AddressID
INNER JOIN Person.StateProvince S ON A.StateProvinceID=S.StateProvinceID
Dacă folosim direct ROLLUP, astfel:
SELECT VendorName, City, StateProvinceName, CountryRegionCode, SUM(TotalDue) AS TotalDue
FROM Purchasing.PurchasesByVendorsAndStates
GROUP BY CountryRegionCode, StateProvinceName, City, VendorName WITH ROLLUP
Atunci obţinem un rezultat de genul:
VendorName City StateProvinceName CountryRegionCode TotalDue
------------------------------------ ---------------- ------------------- ----------------- ---------------------
Greenwood Athletic Company Lemon Grove Arizona US 2472770.0523
NULL Lemon Grove Arizona US 2472770.0523
NULL NULL Arizona US 2472770.0523
Allenson Cycles Altadena California US 498589.5915
Gardner Touring Cycles Altadena California US 25633.6353
NULL Altadena California US 524223.2268
Cruger Bike Company Berkeley California US 31499.8141
Trikes, Inc. Berkeley California US 1461653.945
NULL Berkeley California US 1493153.7591
[...]
American Bicycles and Wheels West Covina California US 9641.0145
Bloomington Multisport West Covina California US 8243.9511
International Bicycles West Covina California US 1589172.9591
International Trek Center West Covina California US 24070.75
NULL West Covina California US 1631128.6747
NULL NULL California US 31564099.5102
[...]
Team Athletic Co. Spokane Washington US 436401.90
Varsity Sport Co. Spokane Washington US 52176.445
NULL Spokane Washington US 488578.345
Electronic Bike Repair & Supplies Tacoma Washington US 2154773.3718
NULL Tacoma Washington US 2154773.3718
NULL NULL Washington US 18899318.6589
NULL NULL NULL US 70479332.6383
NULL NULL NULL NULL 70479332.6383
(169 row(s) affected)
Dacă folosim următorul query:
SELECT VendorName, City, StateProvinceName, CountryRegionCode, SUM(TotalDue) AS TotalDue
FROM Purchasing.PurchasesByVendorsAndStates
GROUP BY CountryRegionCode, StateProvinceName, City, VendorName WITH ROLLUP
HAVING GROUPING(VendorName)=GROUPING(City)
atunci obţinem un rezultat de tipul următor:
VendorName City StateProvinceName CountryRegionCode TotalDue
----------------------------------- ---------------- ------------------ ----------------- ---------------------
Greenwood Athletic Company Lemon Grove Arizona US 2472770.0523
NULL NULL Arizona US 2472770.0523
Allenson Cycles Altadena California US 498589.5915
Gardner Touring Cycles Altadena California US 25633.6353
Cruger Bike Company Berkeley California US 31499.8141
Trikes, Inc. Berkeley California US 1461653.945
[...]
American Bicycles and Wheels West Covina California US 9641.0145
Bloomington Multisport West Covina California US 8243.9511
International Bicycles West Covina California US 1589172.9591
International Trek Center West Covina California US 24070.75
NULL NULL California US 31564099.5102
[...]
Team Athletic Co. Spokane Washington US 436401.90
Varsity Sport Co. Spokane Washington US 52176.445
Electronic Bike Repair & Supplies Tacoma Washington US 2154773.3718
NULL NULL Washington US 18899318.6589
NULL NULL NULL US 70479332.6383
NULL NULL NULL NULL 70479332.6383
(107 row(s) affected)
fiind eliminate astfel subtotalurile pe fiecare oraş, păstrând doar subtotalurile după stat, ţară şi totalul general.
Răzvan