joining and summing two recordsets into one

[obuspider]

I have two separate queries that I need to somehow combine into one recordset. I actually need to sum the total meals and the total working hours. however, the first query yields 37 records while the other only yields 2. How can I combine the two so that I get one recordset with the total meals and total hours each summed separatly?

Select schools.site, schools.area,
schcat, schoolName, d_tot_meals as 'total meals'
From schools, tbl_scanned_sales
Where schools.site = '003'
and month(sc_sales_date) = '9'
and tbl_scanned_sales.sc_site=schools.site
Group By schools.site,
schools.area, schcat, SchoolName, d_tot_meals

Select schools.site, schools.area,
schcat, schoolName, total_hours as 'total hours'
From schools, tbl_empl_trans
Where schools.site = '003'
and month(week_ending_date) = '9'
and tbl_empl_trans.site=schools.site
Group By schools.site,
schools.area, schcat, SchoolName, total_hours

 

[obuspider]

Okay,

I did manage to get the following to work. Is there a way to write it so that when the recordset comes up, it doesn't list two site colums though?

Select * from (Select schools.site, schools.area,
schcat, schoolName, sum(d_tot_meals) as 'total meals'
From schools, tbl_scanned_sales
Where sc_site = '003'
and month(sc_sales_date) = '9'
and tbl_scanned_sales.sc_site=schools.site
Group By schools.site,
schools.area, schcat, SchoolName)a,
(Select schools.site, sum(total_hours) as 'total hours'
From schools, tbl_empl_trans
Where schools.site = '003'
and month(week_ending_date) = '9'
and tbl_empl_trans.site=schools.site
Group By schools.site,
schools.area, schcat, SchoolName)b where a.site = b.site

 

[rac2]

On the face of it I would say that you only get two rows in the second query because there are only two rows per school in tbl_empl_trans with a week_ending_date in September. That would make sense if employee hours are recorded bi-weekly.

Would this query produce the answer you seek?

SELECT schools.site,
       schools.area,
       schcat,
       schoolName,
       SUM(d_tot_meals) as 'total meals',
       SUM(total_hours) as 'total hours'

FROM schools

LEFT JOIN tbl_scanned_sales ON 
tbl_scanned_sales.sc_site=schools.site
   AND month(sc_sales_date) = '9' 

LEFT JOIN tbl_empl_trans ON
tbl_empl_trans.site=schools.site 
   AND month(week_ending_date) = '9'
    
WHERE schools.site = '003' 
GROUP BY schools.site, schools.area, schcat, SchoolName

May I refer you to another post with information that may help.

thread 220-439297

 

[obuspider]

rac2,

Thanks. It didn't quite work right, but I think I have it now.