clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2591518 fiddles created (45738 in the last week).

SELECT version();
version()
5.6.45
 hidden batch(es)


create table jobs( jobid int(7), datein date, total numeric(5,2), chargeto int(5) );
 hidden batch(es)


create table payments( payid int(7), paymentamount numeric(5,2), paymentjobno int(5), paymentdate date, paymenttype int(1) );
 hidden batch(es)


insert into jobs values (1000, '2019-10-01', 100, 4); insert into jobs values (1001, '2019-10-01', 50, 4); insert into jobs values (1002, '2019-10-01', 25, 4); insert into jobs values (1003, '2019-10-01', 220, 6); insert into jobs values (1004, '2019-10-01', 200, 7);
 hidden batch(es)


insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (1, 100, 1000, '2019-10-01', 1); insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (2, 50, 1001, '2019-10-01', 1); insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (3, 25, 1002, '201-10-01', 1); insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (4, 220, 1003, '2019-10-01', 1); insert into payments (payid, paymentamount, paymentjobno, paymentdate, paymenttype) values (5, 200, 1004, '2019-10-01', 1);
 hidden batch(es)


-- SELECT * FROM payments;
 hidden batch(es)


SELECT j.chargeto AS ct, SUM(p.paymentamount) AS p_total FROM jobs j JOIN payments p ON j.jobid = p.paymentjobno -- WHERE j.chargeto IN (1, 2, 4) -- you have AND (payments.paymenttype = 1 OR payments.paymenttype = 2 OR payments.paymenttype = 4) -- AND <add predicate here...> -- add as many as you like GROUP BY j.chargeto -- Now, I have the totals for each chargeto value, so now I can join this to all -- jobids which have smaller values which make up this total.
ct p_total
4 175.00
6 220.00
7 200.00
 hidden batch(es)


-- Desired result - only want ct = 4 - `IN (1, 2, 4)` from OP's fiddle -- in the comment to my first answer. -- 1000, 100, 175 -- 1001, 50, 175 -- 1002, 25, 175
 hidden batch(es)


SELECT j.jobid, p.paymentamount, t1.p_total, CONCAT(j.jobid, ', ', p.paymentamount, ', ', t1.p_total) AS csv -- I advise against this! FROM jobs j JOIN ( SELECT j.chargeto AS ct, SUM(p.paymentamount) AS p_total FROM jobs j JOIN payments p ON j.jobid = p.paymentjobno WHERE j.chargeto IN (1, 2, 4) -- you have AND (payments.paymenttype = 1 OR payments.paymenttype = 2 OR payments.paymenttype = 4) -- AND <add predicate here...> -- add as many as you like GROUP BY j.chargeto ) AS t1 ON j.chargeto = t1.ct JOIN payments p ON j.jobid = p.paymentjobno
jobid paymentamount p_total csv
1000 100.00 175.00 1000, 100.00, 175.00
1001 50.00 175.00 1001, 50.00, 175.00
1002 25.00 175.00 1002, 25.00, 175.00
 hidden batch(es)