SUM() not working with INNER JOIN

[Ryan]

This is the first time I have run into this problem. My MSSQL is as follows:

SELECT p.name, r.res_id, py.payment_total, sum(py.payment_total) AS
payment_net
FROM bat_packs p INNER JOIN bat_res r ON p.pack_id = r.pack_id INNER JOIN
bat_payments py ON r.res_id = py.res_id
WHERE r.res_id = 1194
GROUP BY p.name, r.res_id, py.payment_total

I recieve all the current information except the corrent payment_net, which
is supposed to the SUM of the payment_total column. What I am actually
getting is the exact same amount as the payment_total per row. What's going
on??

[Luuk]

Post by Ryan
SELECT p.name, r.res_id, py.payment_total, sum(py.payment_total) AS
payment_net
FROM bat_packs p INNER JOIN bat_res r ON p.pack_id = r.pack_id INNER JOIN
bat_payments py ON r.res_id = py.res_id
WHERE r.res_id = 1194
GROUP BY p.name, r.res_id, py.payment_total
I recieve all the current information except the corrent payment_net, which
is supposed to the SUM of the payment_total column. What I am actually
getting is the exact same amount as the payment_total per row. What's going
on??

Your GROUP BY is wrong, it should be:
GROUP BY p.name, r.res_id

and not:
GROUP BY p.name, r.res_id, py.payment_total

[Ryan]

By removing the py.payment_total from the GROUP BY statement I recieve this
error:
Column 'py.payment_total' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.

Post by Ryan

Post by Ryan
SELECT p.name, r.res_id, py.payment_total, sum(py.payment_total) AS
payment_net
FROM bat_packs p INNER JOIN bat_res r ON p.pack_id = r.pack_id INNER JOIN
bat_payments py ON r.res_id = py.res_id
WHERE r.res_id = 1194
GROUP BY p.name, r.res_id, py.payment_total
I recieve all the current information except the corrent payment_net, which
is supposed to the SUM of the payment_total column. What I am actually
getting is the exact same amount as the payment_total per row. What's going
on??

GROUP BY p.name, r.res_id
GROUP BY p.name, r.res_id, py.payment_total

[Luuk]

Post by Ryan
By removing the py.payment_total from the GROUP BY statement I recieve this
Column 'py.payment_total' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.

Post by Ryan

Post by Ryan
SELECT p.name, r.res_id, py.payment_total, sum(py.payment_total) AS
payment_net
FROM bat_packs p INNER JOIN bat_res r ON p.pack_id = r.pack_id INNER JOIN
bat_payments py ON r.res_id = py.res_id
WHERE r.res_id = 1194
GROUP BY p.name, r.res_id, py.payment_total
I recieve all the current information except the corrent payment_net, which
is supposed to the SUM of the payment_total column. What I am actually
getting is the exact same amount as the payment_total per row. What's going
on??

GROUP BY p.name, r.res_id
GROUP BY p.name, r.res_id, py.payment_total

SELECT p.name, r.res_id, py.payment_total, sum(py.payment_total) AS
payment_net
FROM bat_packs p INNER JOIN bat_res r ON p.pack_id = r.pack_id INNER JOIN
bat_payments py ON r.res_id = py.res_id
WHERE r.res_id = 1194
GROUP BY p.name, r.res_id, py.payment_total


you are SELECTing py.payment_total AND sum(py.payment_total)

change SELECT to :
SELECT p.name, r.res_id, sum(py.payment_total) AS payment_net
FROM bat_packs p INNER JOIN bat_res r ON p.pack_id = r.pack_id INNER JOIN
bat_payments py ON r.res_id = py.res_id
WHERE r.res_id = 1194
GROUP BY p.name, r.res_id

or change it to:
SELECT p.name, r.res_id, py.payment_total
FROM bat_packs p INNER JOIN bat_res r ON p.pack_id = r.pack_id INNER JOIN
bat_payments py ON r.res_id = py.res_id
WHERE r.res_id = 1194
GROUP BY p.name, r.res_id, py.payment_total

but probably you want the first option ;-)

[Ryan]

Simplified:

SELECT payment_total, SUM(payment_total) AS payment_net
FROM bat_payments
GROUP BY payment_total

I want:
100, 900
300, 900
200, 900
150, 900
150, 900

But what I get is:
100, 100
300, 300
200, 200
150, 300

Next, if I remove the GROUP BY, I recieve the following error:
Column 'payment_total' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.

Please help.