Records must be in this specific order: OnlineDotCom (Company totals)Memphis (District totals)0001 (storeNbr totals)0002 (storeNbr totals)Tupelo (districtName totals)0003 (storeNbr totals)0004 (storeNbr totals)

SELECT isnull(CAST(storenbr AS VARCHAR(50)),isnull(stores.districtname,stores.companyname)) AS Grp ,SUM(Mature) as Mature ,Sum(Cooking) as Cooking ,Sum(Exercise) as Exercise ,Sum(Math) as Math ,Sum(AudioBooks) as AudioBooks ,Sum(Childrens) as Childrens ,Sum(Spanish) as Spanish from booksales sales inner join bookstores stores on stores.storeid = sales.storeid INNER JOIN ( SELECT [COMPANYNAME],[DISTRICTNAME],MIN([STORENBR]) AS MIN_STORENBR FROM bookstores GROUP BY [COMPANYNAME],[DISTRICTNAME] ) stores2 ON stores2.[COMPANYNAME] = stores.[COMPANYNAME] AND stores2.[DISTRICTNAME] = stores.[DISTRICTNAME]group BY stores.companyname , stores.districtname , MIN_STORENBR , STORES.STORENBR WITH rollup HAVING ISNULL(MIN_STORENBR,stores.storenbr) IS NOT NULL OR stores.companyname IS NOT NULL AND stores.districtname IS NULL ORDER BY isnull(MIN_STORENBR,' ' + stores.companyname), isnull(storenbr,' ' + isnull(stores.districtname,' '+ stores.companyname))

SELECT companyname , districtname , storenbr , businessdate , Mature , Math , AudioBooks from booksales sales inner join bookstores stores on stores.storeid = sales.storeid union allSELECT stores.companyname as companyname , stores.districtname as districtname , '' as storenbr , businessDate ,SUM(Mature) as Mature ,Sum(Math) as Math ,Sum(AudioBooks) as AudioBooks from booksales sales inner join bookstores stores on stores.storeid = sales.storeid group by companyname, districtname, businessdate union all SELECT stores.companyname as companyname , '' as districtname , '' as storenbr , businessdate ,SUM(Mature) as Mature ,Sum(Math) as Math ,Sum(AudioBooks) as AudioBooks from booksales sales inner join bookstores stores on stores.storeid = sales.storeid group by companyname, businessdateorder by companyname, districtname, storenbr, businessdate

SELECT stores.companyname as companyname , '' as districtname , '' as storenbr , CONVERT(VARCHAR(9), businessdate, 6) as businessdate ,SUM(Mature) as Mature ,Sum(Math) as Math ,Sum(AudioBooks) as AudioBooks from booksales sales inner join bookstores stores on stores.storeid = sales.storeid group by companyname, CONVERT(VARCHAR(9), businessdate, 6)order by companyname, districtname, storenbr