I have two tables in my database. One has shipment data with zip code location and the other table has a complete zip code listing of all zips in the US. I just included the relevant columns below to keep it simple.
Table: ZipCodes
- ZipCode PK char(5)
Table: Shipments
- ShipToZip varchar(10)
- AMT decimal(10,2)
So some of the shipment data may be to non-US addresses, and others may include the +4 for US zip codes (XXXXX-XXXX) which is why the shipment table has up to 10. All I need to do is export the query as a CSV, however I need the zip codes converted to integer.
SELECT CAST(ShipToZIP AS INT) AS [target],SUM(cast(AMT as int)) AS [total]
FROM
(SELECT LEFT(ShiptoZip,5) ShipToZip,Amt
FROM Shipments
WHERE LEFT(SHIPTOZIP,5) IN (SELECT zipcode from ZipCodeLocations)
) Q
GROUP BY CAST(ShipToZIP AS INT)
When I run that query however, I keep getting conversion errors for the Canadian zip codes. If I run the inner Q query by itself, every single record is a valid US only code.
How can I force the subquery to run first? I tried making it a CTE per this post however it didn't work either.