Creating effective report using Roll UP in SQL SERVER 2008

This example shows you how to use ROLL UP to create a report that return sum of stock,sale and amount on the basis of Product_Group and Product_Code.

drop table [#tmp]
CREATE TABLE [dbo].[#tmp](
    [Product_Group] [varchar](1000) NULL,
    [Product_Code] [varchar](1000) NULL,
    [Product_Name] [varchar](1000) NULL,
    [Stock] [int] NULL,
    [Sale] [int] NULL,
    [Amt] [numeric](18, 0) NULL
)


 Insert Into #tmp (Product_Group, Product_Code, Product_Name, Stock, Sale, Amt)
 Values ('Stationary', 'A12', 'Pencil', 63, 30, 300),
 ('Stationary', 'A13', 'Pen', 83, 61, 534),
 ('Furniture', 'F11', 'Table', 113, 105, 68000),
 ('Furniture', 'F12', 'Chair', 62, 55, 55234)

 --select * from #tmp

; with result
as
(
 select Product_Group, Product_Code, Product_Name, Sum(Stock) as Stock, Sum(Sale) as Sale, Sum(Amt) as Amt from #tmp
group by ROLLUP
(Product_Group,Product_code,Product_name)
)

select isnull(Product_Group,'Grand Total:'),
(case when product_group is null and product_code is null then '' else isnull(Product_Code,'Sub-totals:') end) Product_code ,
isnull(Product_Name,'') as Product_Name, Stock, Sale, Amt
 from result
where (product_name is not null and product_code is not null)
or
(product_name is null and product_code is null )

this is the result set returns for this query

Ties, Rank, Row_number function

I have used ranked function to find and remove duplicate rows in my procedures but i have found another option that may be more feasible with my queries. Sql server introduce Ties function that return one value from a column that has duplicate values as we can see in following example

Declare @Table table(id int ,brand varchar(5),price money)

insert into @Table

values (1,'GE',20),(2,'GE',21),(3,'Sony',21)

;with dup

as

(

select rank() over(partition by brand order by price) as dup,* from @Table

)

select * from dup where dup=1

SELECT Top(1) With Ties * 

FROM @Table

WHERE price between 19 and 21 

Order By ROW_NUMBER() OVER (PARTITION By Brand ORDER BY price)

results for both queries are same but this is good use of Ties function in queries.