This following query gives me exactly what I need except the companyname and districtname columns need to be eliminated

1) when the districtname is empty, the companyname becomes the districtname

2) when the storenbr is empty the districtname becomes the storenbr

The districts and companies are all unique and storenbrs are all unique ,

the client applicaiton will know that a particular record represents a store, a district,

or a company

SELECT companyname

, districtname

, storenbr

, businessdate

, Mature

, Math

, AudioBooks

from booksales sales

inner join bookstores stores on stores.storeid = sales.storeid

union all

SELECT 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, businessdate

order by companyname, districtname, storenbr, businessdate

Of course, this can be simplified with rollup.

Note:

-- all columns in my original query were not necessary

-- the businessdate needs to be in dd-Mon-yy format