Rob-350472 (4/15/2013)
Guys,I've created a temp table which has a bunch of IDs and dates, something dead simple like this:
SELECT
Pg.ID,
pg.Name,
COUNT(pa.Log_ID) as A,
FROM tbla pg
JOIN #log pa on pa.ID = pg.ID
GROUP BY pg.id, pg.name
Works just fine, if I try to join to the same table again (with a different alias) and do another count it gives me an error:
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
SELECT
Pg.ID,
pg.Name,
COUNT(pa.Log_ID) as A,
COUNT(pb.Log_ID) as B,
FROM tbla pg
JOIN #log pa on pa.ID = pg.ID
JOIN #log pb on pb.ID = pg.ID
GROUP BY pg.id, pg.name
My initial reason for this double join was thinking I could restrict based on date - so whilst my temp table has the data going back over 1 year I could join to it several times, restricting my data set on each join - e.g. once for the full year, once for 6 months, once for 3 months etc.
I've not come across this before, does anyone know what might be causing it? I tried casting to a bigint but it just ran for ages (well over 10mins when the origina query takes seconds).
Thanks!
Your query was counting more than 2,147,483,647 rows! If you're interested in a workaround - probably not - COUNT_BIG() would do the trick.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden