'Arithmetic overflow error converting expression to data type int' when left join temp tables

Question

When trying to LEFT JOIN two temp tables I have, I'm getting the arithmetic overflow error. When doing a UNION, there's no issue, nor is there an issue when I change my SELECT statement to not use SUM functions. Here are my two tables:

SELECT SUM(count) count
     , EventType
     , month
FROM #engine_final
GROUP BY EventType
       , month
UNION ALL
SELECT SUM(count) count
     , EventType
     , month
FROM #circumvent_final
GROUP BY EventType
       , month

And here's the results:

So what I'm attempting to do is SUM my counts, grouped by month, with the following query:

SELECT SUM(ef.count) AS EngineStarts
     , SUM(cf.count) AS Circumventions
FROM #engine_final ef
     LEFT JOIN #circumvent_final cf ON ef.month = cf.month

But this is when I'm confronted with the error. I thought maybe I had hit the limit for INT, but my numbers only reach about 260 million, so that can't be it. What am I missing?

Please in code questions give a minimal reproducible example--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. How to Ask Pause work on the overall goal, chop code to the 1st expression not giving what you expect & say what you expect & why. — philipxy
– philipxy, Commented Jul 24, 2020 at 0:40
PS Please use text, not images/links, for text--including tables & ERDs. Use images only for what cannot be expressed as text or to augment text. Include a legend/key & explanation with an image. — philipxy
– philipxy, Commented Jul 24, 2020 at 0:40

GMB · Accepted Answer · 2020-07-23 22:50:50Z

1

Your join multiplies the rows. You should pre-aggregate in subqueries first, then join:

SELECT ef.month, ef.EngineStarts, cf.Circumventions
FROM (
    SELECT month, SUM(count) EngineStarts
    FROM #engine_final 
    GROUP BY month
) ef
LEFT JOIN (
    SELECT month, SUM(cf.count) AS Circumventions
    FROM #circumvent_final 
    GROUP BY month
) cf ON ef.month = cf.month

I am unsure whether you need where clauses in the subquery to filter on the eventType - you can easily add them if that's needed.

If you have months in cf that are not in ef, and the other way around too, you might want to consider a FULL JOIN instead of a LEFT JOIN:

SELECT 
    COALESCE(ef.month, cf.month) month, 
    COALESCE(ef.EngineStarts, 0) EngineStarts, 
    COALESCE(cf.Circumventions, 0) Circumventions
FROM (
    SELECT month, SUM(count) EngineStarts
    FROM #engine_final 
    GROUP BY month
) ef
FULL JOIN (
    SELECT month, SUM(cf.count) AS Circumventions
    FROM #circumvent_final 
    GROUP BY month
) cf ON ef.month = cf.month

answered Jul 23, 2020 at 22:50

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jw11432 Over a year ago

Outstanding, thank you so much. I don't understand why my original method was "multiplying" the rows, however. Might I bother you for an explanation of that? I thought it was simply going to SUM the counts based on their existence with the correct month?

GMB Over a year ago

@jw11432: there are multiple rows in both tables that have the same month, so the join generates a cartesian products of these. So you end up summing the same value multiple times instead of just once.

Collectives™ on Stack Overflow

'Arithmetic overflow error converting expression to data type int' when left join temp tables

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related