--1--
--rollup--
SELECT Concat(t.I,' ',t.N) AS "Klient", t.Rok AS [Rok], SUM(t.m) AS Kwota
FROM (  SELECT P.BusinessEntityID AS "pid" , P.FirstName AS I, P.LastName AS N, YEAR(SOH.OrderDate) AS Rok, SOD.LineTotal AS M
    FROM Person.Person AS "P"
        JOIN Sales.Customer AS "C" ON(C.PersonID = P.BusinessEntityID)
        LEFT JOIN Sales.SalesOrderHeader AS "SOH" ON(C.CustomerID = SOH.CustomerID)
        JOIN Sales.SalesOrderDetail AS "SOD" ON(SOH.SalesOrderID = SOD.SalesOrderID))
                AS "t"
GROUP BY ROLLUP(Concat(t.I,' ',t.N), [Rok]);

--cube--
SELECT Concat(t.I,' ',t.N) AS "Klient", t.Rok AS [Rok], SUM(t.m) AS Kwota
FROM (  SELECT P.BusinessEntityID AS "pid" , P.FirstName AS I, P.LastName AS N, YEAR(SOH.OrderDate) AS Rok, SOD.LineTotal AS M
    FROM Person.Person AS "P"
        JOIN Sales.Customer AS "C" ON(C.PersonID = P.BusinessEntityID)
        LEFT JOIN Sales.SalesOrderHeader AS "SOH" ON(C.CustomerID = SOH.CustomerID)
        JOIN Sales.SalesOrderDetail AS "SOD" ON(SOH.SalesOrderID = SOD.SalesOrderID))
                AS "t"
GROUP BY Concat(t.I,' ',t.N), CUBE([Rok]);

--grouping set--
SELECT Concat(t.I,' ',t.N) AS "Klient", t.Rok AS [Rok], SUM(t.m) AS Kwota
FROM (  SELECT P.BusinessEntityID AS "pid" , P.FirstName AS I, P.LastName AS N, YEAR(SOH.OrderDate) AS Rok, SOD.LineTotal AS M
    FROM Person.Person AS "P"
        JOIN Sales.Customer AS "C" ON(C.PersonID = P.BusinessEntityID)
        LEFT JOIN Sales.SalesOrderHeader AS "SOH" ON(C.CustomerID = SOH.CustomerID)
        JOIN Sales.SalesOrderDetail AS "SOD" ON(SOH.SalesOrderID = SOD.SalesOrderID))
                AS "t"
GROUP BY GROUPING SETS(Concat(t.I,' ',t.N), (Concat(t.I,' ',t.N),[Rok]));


--2--
--rollup--
SELECT t.Kat AS [Kategoria], t.Pro AS Produkt, t.Rok AS [Rok], SUM(t.Znizka) AS Kwota
FROM (SELECT YEAR(SOH.OrderDate) AS Rok, PC.Name AS Kat, P.Name AS Pro, SOD.UnitPriceDiscount AS Znizka
    FROM Sales.SalesOrderHeader AS SOH
        JOIN Sales.SalesOrderDetail AS SOD ON(SOH.SalesOrderID = SOD.SalesOrderID)
        RIGHT JOIN Production.Product AS P ON(SOD.ProductID = P.ProductID)
        LEFT JOIN Production.ProductSubcategory AS PSC ON(P.ProductSubcategoryID = PSC.ProductSubcategoryID)
        JOIN Production.ProductCategory AS PC ON(PSC.ProductCategoryID = PC.ProductCategoryID))
    AS t
GROUP BY ROLLUP(t.Kat, t.Pro, [Rok]);

--cube--
SELECT t.Kat AS [Kategoria], t.Pro AS Produkt, t.Rok AS [Rok], SUM(t.Znizka) AS Kwota
FROM (SELECT YEAR(SOH.OrderDate) AS Rok, PC.Name AS Kat, P.Name AS Pro, SOD.UnitPriceDiscount AS Znizka
    FROM Sales.SalesOrderHeader AS SOH
        JOIN Sales.SalesOrderDetail AS SOD ON(SOH.SalesOrderID = SOD.SalesOrderID)
        RIGHT JOIN Production.Product AS P ON(SOD.ProductID = P.ProductID)
        LEFT JOIN Production.ProductSubcategory AS PSC ON(P.ProductSubcategoryID = PSC.ProductSubcategoryID)
        JOIN Production.ProductCategory AS PC ON(PSC.ProductCategoryID = PC.ProductCategoryID))
    AS t
GROUP BY t.kat, t.Pro, CUBE([Rok]);

--grouping sets--
SELECT t.Kat AS [Kategoria], t.Pro AS Produkt, t.Rok AS [Rok], SUM(t.Znizka) AS Kwota
FROM (SELECT YEAR(SOH.OrderDate) AS Rok, PC.Name AS Kat, P.Name AS Pro, SOD.UnitPriceDiscount AS Znizka
    FROM Sales.SalesOrderHeader AS SOH
        JOIN Sales.SalesOrderDetail AS SOD ON(SOH.SalesOrderID = SOD.SalesOrderID)
        RIGHT JOIN Production.Product AS P ON(SOD.ProductID = P.ProductID)
        LEFT JOIN Production.ProductSubcategory AS PSC ON(P.ProductSubcategoryID = PSC.ProductSubcategoryID)
        JOIN Production.ProductCategory AS PC ON(PSC.ProductCategoryID = PC.ProductCategoryID))
    AS t
GROUP BY GROUPING SETS ((t.kat,t.pro), (t.kat, t.pro, [Rok]));