469,272 Members | 1,400 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,272 developers. It's quick & easy.

SUM does not return expected results when used with JOIN

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
Jul 19 '05 #1
2 1934
"Richard J Lacroix" <ri**********@comcast.net> wrote in message
news:20**************************@posting.google.c om...
[snip]
What is the purpose of repeating `id_ts_inv` in the FROM?
FROM `id_ts_inv`
`id_ts_inv` LEFT JOIN `id_es_inv` USING (`Employee_SSN`)


--
Eric Lafontaine
Jul 19 '05 #2
That's just a cut-and-paste error.

Richard

"ventre--pattes" <ne**@ventre-a-pattes.com> wrote in message news:<be*********@imsp212.netvigator.com>...
"Richard J Lacroix" <ri**********@comcast.net> wrote in message
news:20**************************@posting.google.c om...
[snip]
What is the purpose of repeating `id_ts_inv` in the FROM?
FROM `id_ts_inv`
`id_ts_inv` LEFT JOIN `id_es_inv` USING (`Employee_SSN`)

Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Hunter Hillegas | last post: by
25 posts views Thread by Peter Michaux | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.