I have two tables that I am trying to use to create a report by using
a join. When the query is executed, the SUM on the second table is 4
times the expected amount. As a stand alone query on the second table,
this works:
SELECT `Last_Name`, `First_Name`, `Employee_SSN`,
SUM(`id_es_inv`.`Total_Amount`) AS `Expense Amount`
FROM `id_es_inv`
GROUP BY `Last_Name`
But when I attempt the same operation using a JOIN on the first table,
the SUM then comes out as 4x the expected value:
SELECT
SUM(`id_es_inv`.`Total_Amount`) AS `Expense Amount`,
CONCAT_WS(', ',id_ts_inv.`Last_Name`,id_ts_inv.`First_Name`) AS
`Name`, SUM(id_ts_inv.Total_Amount) As `Total`,
SUM(id_ts_inv.`Hours_Reg`) AS `REG Hrs`, SUM(id_ts_inv.`Hours_OT`) AS
`OT Hrs`, SUM(id_ts_inv.`Hours_DT`) AS `DT Hrs`,
id_ts_inv.`Rate_Regular_Bill` AS `Bill Rate`,
SUM(id_ts_inv.`Hours_Reg`* id_ts_inv.`Rate_Regular_Bill`) AS `Amount
ST`,
SUM(id_ts_inv.`Hours_OT`* id_ts_inv.`Rate_Regular_Bill`) AS `Amount
OT`,
SUM(id_ts_inv.`Hours_DT`* id_ts_inv.`Rate_Regular_Bill`) AS `Amount
DT`
FROM `id_ts_inv`
`id_ts_inv` LEFT JOIN `id_es_inv` USING (`Employee_SSN`)
GROUP By id_ts_inv.`Last_Name`, `id_es_inv`.`Last_Name`
The tables are not identicle in structure, i.e `id_es_inv` contains a
smaller number of columns and rows than `id_ts_inv`, so I can't use a
UNION with 2 selects. I've also tried changing the JOIN columns, but
that just made things worse.
I've poured over the Internet, gone through everything relevant in
Paul DuBois's MySQL CookBook, asked friends who have more MySQL
experience that I do, but no luck yet.
Can anybody help with a useful suggestion as to why this behavior is
happening?
TIA
