Currently, I am using count function to calculate the value for 2 columns and wanna substract the 2 columns and export to excel file.
This is my code. I think the bold statement contains logical error.
SELECT software.*, (SELECT count(lkey.k_id) FROM lkey WHERE lkey.s_id = software.s_id) AS Total, (SELECT count(assign.k_id) FROM assign WHERE assign.s_id = software.s_id) AS Installed, **(SELECT count(lkey.k_id) - count(assign.k_id) FROM lkey INNER JOIN assign WHERE lkey.k_id = assign.k_id GROUP BY lkey.k_id) AS Available** FROM software";
Advertisement
Answer
I think you want two LEFT JOIN
s on aggregate queries:
SELECT s.*, COALESCE(l.total, 0) total, COALESCE(a.installed, 0) installed, COALESCE(l.total, 0) - COALESCE(a.installed, 0) available FROM software s LEFT JOIN (SELECT s_id, count(*) total FROM lkey GROUP BY s_id) l ON l.s_id = s.s_id LEFT JOIN (SELECT s_id, count(*) installed FROM assign GROUP BY s_id) a ON a.s_id = s.s_id
In very recent versions of MySQL, you could use lateral joins:
SELECT s.*, l.total, a.installed, l.total - a.installed available FROM software s LEFT JOIN LATERAL (SELECT count(*) total FROM lkey l WHERE l.s_id = s.s_id) l ON 1 LEFT JOIN LATERAL (SELECT count(*) installed FROM assign a WHERE a.s_id = s.s_id) a ON 1