mysql query on two tables , sum on one table and match with other table

[jkkenzie]

Hi!

i have two tables:

Table "a" has the following records:

 

| program | Activity | expense | transDate

---------------------------------------------------------

      1        |     1      |     2000   |   yyyy/mm/dd H:m:s

      2        |     3      |    234      |   yyyy/mm/dd H:m:s

      1        |     1      |    234      |   yyyy/mm/dd H:m:s

      2        |     3      |    234      |   yyyy/mm/dd H:m:s

 

 

Table "b" has the following records

 

| program | Activity | approved| approveDate

---------------------------------------------------------

      1        |     1      |     3000   |   yyyy/mm/dd H:m:s

      2        |     3      |    5034    |   yyyy/mm/dd H:m:s

      6        |     1      |    1234    |   yyyy/mm/dd H:m:s

      4        |     2      |    2304    |   yyyy/mm/dd H:m:s

 

 

what i want to achieve is :

Select all records in table "a" and group them so that a group has same "program" and "Activity" number and of the same YEAR THEN sum the expense for each group . the sum found is then subtracted from the Table "b" Approved amount WHERE "Program" and "Activity" in both tables are the same.

 

results:

| program | Activity | approved| TotalEpense  | Difference | YearOfApprovalAndUse

------------------------------------------------------------------------------------------------------

      1        |     1      |     3000   |      2234        |      766       |  yyyy   (we indicate the year extracted from the transaction date)

      2        |     3      |     5034   |      468          |      4566     |  yyyy   (we indicate the year extracted from the transaction date)

 

 

How is it possible through a mysql query?

 

thanks in advance.

 

 

 

[Psycho]

Are there duplicates in table "b" with the same program, activity and year? If not, the query will be relatively simple:

 

SELECT b.program, b.Activity, b.approved, SUM(a.expense) as TotalEpense,
       (b.approved-SUM(a.expense)) AS Difference, YEAR(b.approveDate) as YearOfApprovalAndUse

FROM b

JOIN a ON b.program = a.program
      AND b.Activity = a.Activity
      AND YEAR(a.transDate) = YEAR(b.approveDate)

GROUP BY b.program, b.Activity, YearOfApprovalAndUse

 

 

If not, the query is a little more complicated:

 

SELECT a2.program, a2.Activity, SUM(b.approved) as approved, a2.TotalEpense,
       (SUM(b.approved)-a2.TotalEpense) as Difference, a2.YearOfApprovalAndUse

FROM (SELECT a.program, a.Activity, SUM(a.expense) as TotalEpense,
             YEAR(a.transDate) as YearOfApprovalAndUse
      FROM a
      GROUP BY program, Activity, YearOfApprovalAndUse) as a2

JOIN b
   ON a2.program = b.program
  AND a2.Activity = b.Activity
  AND a2.YearOfApprovalAndUse = YEAR(b.approveDate)

GROUP BY a2.program, a2.Activity, a2.YearOfApprovalAndUse

[jkkenzie]

Hi, Good stuff! thanks, the second one works: Look at the final query:

SELECT expense_entries2.progid, expense_entries2.actid, expense_entries2.itemid, expense_entries2.subitemid, expense_entries2.subitemcatid,expense_entries2.expenseid, 
SUM(approvals.approved) as approved, expense_entries2.TotalEpense,
(SUM(approvals.approved)-expense_entries2.TotalEpense) as Difference, expense_entries2.YearOfApprovalAndUse
FROM (SELECT expense_entries.progid, expense_entries.actid, expense_entries.itemid, expense_entries.subitemid, expense_entries.subitemcatid,expense_entries.expenseid, 
SUM(expense_entries.amount) as TotalEpense, YEAR(expense_entries.transDate) as YearOfApprovalAndUse
FROM expense_entries
GROUP BY progid, actid, itemid, subitemid, subitemcatid, expenseid,  YearOfApprovalAndUse) as expense_entries2
JOIN approvals
   ON expense_entries2.progid = approvals.progid
  AND expense_entries2.actid = approvals.actid 
  AND expense_entries2.itemid = approvals.itemid 
  AND expense_entries2.subitemid = approvals.subitemid
  AND expense_entries2.subitemcatid = approvals.subitemcatid
  AND expense_entries2.expenseid = approvals.expenseid
  AND expense_entries2.YearOfApprovalAndUse = approvals.budget_year
GROUP BY expense_entries2.progid, expense_entries2.actid, expense_entries2.itemid, expense_entries2.subitemid, expense_entries2.subitemcatid,expense_entries2.expenseid, expense_entries2.YearOfApprovalAndUse

- progid comes from Programs table - how do we include a query here to select the ProgramName from Programs table:

- same to actid => activity table

[jkkenzie]

Solved it. Just added more joins ...

 

thanks a million!