Subtract two subqueries
13,865
assuming you have more than one stationID,
SELECT station_id, tcl_total, total, a.tcl_total - total as diff
from (
SELECT station_id, SUM(tcl_missing + tcl_not_missing) AS tcl_total
FROM tcl_missing_summary
GROUP BY station_id
) AS a INNER JOIN
(
SELECT station_id, SUM(total) AS total
FROM tcl_breakdown_op
WHERE tr_standard NOT LIKE '%cru'
GROUP BY station_id
) AS b
ON a.stationid = b.stationid
WHERE a.tcl_total - total <> 0
also consider something like
SELECT
station_id,
SUM(tcl_missing + tcl_not_missing) AS sum_tcl_total,
SUM(total) AS sum_total,
SUM(tcl_missing + tcl_not_missing) - SUM(total) AS diff
FROM
tcl_missing_summary s INNER JOIN
tcl_breakdown_op b ON
s.station_id = b.station_id
WHERE
tr_standard NOT LIKE '%cru'
GROUP BY
station_id
HAVING
SUM(tcl_missing + tcl_not_missing) <> SUM(total)
Author by
dido
Updated on June 07, 2022Comments
-
dido almost 2 years
I have the following queries. I'd like to subtract the numbers from the SUMs and then return only the ones with a result other than 0. Any help would be appreciated. I would also be interested to see if this can be done in one query. I tried it with the HAVING clause but it was returning wrong results. Using SQL Server 2008
SELECT ( SELECT station_id, SUM(tcl_missing + tcl_not_missing) as tcl_total FROM tcl_missing_summary GROUP BY station_id ) as a ( SELECT station_id, SUM(total) as total FROM tcl_breakdown_op WHERE tr_standard not like '%cru' GROUP BY station_id ) as b WHERE a.tcl_total - total <> 0