I was working on report which produce those table which added during last month under each schema. That report has 3 column i.e. Schema Name, Table Name, Current Size.
A additional row for total size also needed and it should be schema wise.
As per solution I have used ROLLUP clause to add additional total column for schema. Once I had done then did some more example for ROLLUP. Here I have shared a example where I have applied ROLLUP in multiple column. ROLLUP used with GROUP BY clause and able to produce result in between all row on the bases of particular columns.
You can easily understand by the following example. Create a table tblProductConfig and insert some rows.
CREATE TABLE tblProductConfig (Category VARCHAR(100),Subcategory VARCHAR(100),Product VARCHAR(100),ProductCost INT)
GO
INSERT INTO tblProductConfig VALUES('fmcg', 'Soap','LUX',9 )
INSERT INTO tblProductConfig VALUES('fmcg', 'Soap','Nirma',8)
INSERT INTO tblProductConfig VALUES('fmcg', Shampoo','Sunsilk',5)
INSERT INTO tblProductConfig VALUES('fmcg', 'Shampoo','Chik',7)
INSERT INTO tblProductConfig VALUES('Electronic', 'TV','Sony',9)
INSERT INTO tblProductConfig VALUES('Electronic', 'TV','Samsung',2)
INSERT INTO tblProductConfig VALUES('Electronic', 'AC','Voltas',1)
INSERT INTO tblProductConfig VALUES('Electronic', 'AC','Haeir',30)
INSERT INTO tblProductConfig VALUES('Computer', 'Harddisk','Seagate',20)
INSERT INTO tblProductConfig VALUES('Computer', 'Harddisk','Samsung',11 )
INSERT INTO tblProductConfig VALUES('Computer', 'CPU','Intel',6)
INSERT INTO tblProductConfig VALUES('Computer', 'CPU','AMD',6)GO
A additional row for total size also needed and it should be schema wise.
As per solution I have used ROLLUP clause to add additional total column for schema. Once I had done then did some more example for ROLLUP. Here I have shared a example where I have applied ROLLUP in multiple column. ROLLUP used with GROUP BY clause and able to produce result in between all row on the bases of particular columns.
You can easily understand by the following example. Create a table tblProductConfig and insert some rows.
CREATE TABLE tblProductConfig (Category VARCHAR(100),Subcategory VARCHAR(100),Product VARCHAR(100),ProductCost INT)
GO
INSERT INTO tblProductConfig VALUES('fmcg', 'Soap','LUX',9 )
INSERT INTO tblProductConfig VALUES('fmcg', 'Soap','Nirma',8)
INSERT INTO tblProductConfig VALUES('fmcg', Shampoo','Sunsilk',5)
INSERT INTO tblProductConfig VALUES('fmcg', 'Shampoo','Chik',7)
INSERT INTO tblProductConfig VALUES('Electronic', 'TV','Sony',9)
INSERT INTO tblProductConfig VALUES('Electronic', 'TV','Samsung',2)
INSERT INTO tblProductConfig VALUES('Electronic', 'AC','Voltas',1)
INSERT INTO tblProductConfig VALUES('Electronic', 'AC','Haeir',30)
INSERT INTO tblProductConfig VALUES('Computer', 'Harddisk','Seagate',20)
INSERT INTO tblProductConfig VALUES('Computer', 'Harddisk','Samsung',11 )
INSERT INTO tblProductConfig VALUES('Computer', 'CPU','Intel',6)
INSERT INTO tblProductConfig VALUES('Computer', 'CPU','AMD',6)GO
Table structure created as
Query for produce total cost on Category and Subcategory bases.
SELECT
case when grouping(Category) = 1 then 'Total' else Category end as Category,
case when grouping(Subcategory) = 1 and grouping(Category) = 0 then 'Category Total' else Subcategory end as Subcategory,
case when grouping(Product) = 1 and grouping(Subcategory) = 0 and grouping(Category) = 0 then 'Subcategory Total' else Product end as Product,
SUM (ProductCost) AS ProductCost
FROM tblProductConfigGROUP
BY Category,Subcategory,Product
WITH ROLLUP
Result set produce by Query with ROLLUP.
Here we can see total product cost by all 3 category and all 6 subcategory.
Suggestions are always welcome.
Thanks
