How to substract 2 columns from different table with using count function?

Question

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"; 

Answer 1

I think you want two LEFT JOINs 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