How to Select Summary and detail together

[donjohnson]

Hello!
I would like to have a single query return a set of records that has (1) a Grand Total for a given date, (2) Subtotals for Date, Customer, Location, and (3) 1 to n Detail records for each (2).

Can this be done in a single query, or how can I combine the results from multiple queries into a single record set?

An example table def has:
Date
Customer
Location
WorkOrder
Hours (to be summed in totals)
Accepted (summed)
Refusals (summed)
Accept/Hour (computed field here, and computed in totals)

Also, I pull a field, Rate from a WorkOrder table to calculate Revenue (Hours * Workorder.rate, also computed at totals), and sum(Revenue)/sum(Hours) at the totals.

Can anyone help or direct me here?

Thanks!

Don

 

[r937]

select Date
     , Customer
     , Location
     , WorkOrder
     , Hours 
     , Accepted 
     , Refusals 
     , Accept/Hour as Accept_per_Hour
  from example
union all
select Date
     , Customer
     , Location
     , null 
     , sum(Hours)
     , sum(Accepted) 
     , sum(Refusals) 
     , sum(Accept)/sum(Hour)
  from example
group
    by Date
     , Customer
     , Location  
union all
select null
     , null
     , null
     , null
     , sum(Hours)
     , sum(Accepted) 
     , sum(Refusals) 
     , sum(Accept)/sum(Hour)
  from example
group
    by Date
     , Customer
     , Location  
order
    by case when Date is null then 1 else 0 end
     , Date
     , Customer
     , Location   
     , case when WorkOrder is null then 1 else 0 end

rudy

http://r937.com/

SQL Consulting

 

[donjohnson]

Thanks for the help, it was the UNION ALL that I didn't understand before - I now have my query giving me what I want!

Thanks again!

Don