Subquery returns more than 1 row error
I am developing a php/MySQL system. The database records actions and time spent on matters. The actions are recorded in the table matterjunactions where the time is recorded as a decimal in the field ‘howlong’ and the hourly fee stored in the field ‘fee’. They are linked to the matters table by matterid. When a set of actions have been billed the field ‘billed’ reads ‘Y’
I have a page that is producing a report from a number of tables including the tables above. It works fine until I try to add the sum of howlong*fee for all of those actions where billed=’N’ when I get the ‘Subquery returns more than 1 row’ error.
The code I have added to get the totals (which is the one that gives the error) is:
(Select ROUND((SUM(howlong*matterjuncactions.fee)),2)
FROM matterjuncactions JOIN matters ON
matters.matterid=matterjuncactions.matterid
WHERE matterjuncactions.billed='N' GROUP BY matters.matterid) AS nonbillsum
The whole query is:
SELECT `ref`, nickname, type, statusnsteps, finalemail, bible, (matterjuncoriginals.sent) AS origsent, GROUP_CONCAT(DISTINCT staff.initials SEPARATOR '; ') AS lds, CONCAT(DATE_FORMAT(matterjuncupdate.updatedate,'%D %M %Y'),' - ',matterjuncupdate.who) AS uptext, (Select GROUP_CONCAT(distinct initials SEPARATOR ', ')
from matterjuncstaff INNER join staff on staff.staffid = matterjuncstaff.staffid
where matterjuncstaff.lead = 'N' AND matterjuncstaff.matterid = matters.matterid) AS otstf,
(Select ROUND((SUM(howlong*matterjuncactions.fee)),2)
FROM matterjuncactions JOIN matters ON matters.matterid=matterjuncactions.matterid
WHERE matterjuncactions.billed='N' GROUP BY matters.matterid) AS nonbillsum
FROM matters
LEFT JOIN matterjuncstaff ON matterjuncstaff.matterid = matters.matterid AND lead = 'Y'
LEFT JOIN staff on matterjuncstaff.staffid=staff.staffid
LEFT JOIN matterjuncupdate ON matters.matterid=matterjuncupdate.matterid
LEFT JOIN matterjuncoriginals ON matters.mattercode=matterjuncoriginals.mattercode
LEFT JOIN matterjuncactions ON matters.mattercode=matterjuncactions.mattercode
WHERE `state` = 'Open' AND type='1'
GROUP BY matters.matterid
ORDER BY nickname ASC
I have looked at a number of answers to the ‘Subquery returns more than 1 row’ error but they haven’t helped me. Somewhere I saw that I should use a join with the subquery but I can’t work out how to get that working. I accept that my code is not the best but any help in solving this would be greatly appreciated.