CODEWITHSUNDEEP
sqlsql-serverselectsubquery
dido
dido

Reputation: 3407

Subtract two subqueries

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

Upvotes: 5

Views: 17931

Answers (4)

Beth
Beth

Reputation: 9607

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)

Upvotes: 4

djangojazz
djangojazz

Reputation: 13232

I agree with rs on the CTE method as nested selects IMHO get nasty after two or three of them. CTE's also show your data logically in groups one at a time.

However if you have to use nested selects, just add a few lines to make it complete:

SELECT a.tcl_total
from 
(
 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 - b.total <> 0

Upvotes: 0

MysteryMoose
MysteryMoose

Reputation: 2363

SELECT a.station_id AS station, a.tcl_total-b.total AS difference
(
 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.station_id = b.station_id
 WHERE a.tcl_total - b.total <> 0

This can also be converted to an outer join if you know that one result set may not have data for some subset of the stations.

Upvotes: 0

rs.
rs.

Reputation: 27427

Try this

; WITH CTE1 AS 
(
SELECT station_id, SUM(tcl_missing + tcl_not_missing) as tcl_total
 FROM tcl_missing_summary 
 GROUP BY station_id
), CTE2 AS
(
SELECT station_id, SUM(total) as total
 FROM tcl_breakdown_op
 WHERE tr_standard not like '%cru'
 GROUP BY station_id
)

SELECT * FROM CTE1 a
LEFT OUTER JOIN CTE2 b ON a.station_id = b.station_id
WHERE a.tcl_total - isnull(b.total,0) <> 0

Upvotes: 0

Related Questions