CODEWITHSUNDEEP
sqlsql-servertoad
Tauseef Hussain
Tauseef Hussain

Reputation: 1079

SQL Server: Issues with data type

SELECT pa.[type], (SUM(pa.Actions_Logged)/SUM(pi.Impressions_Served)) AS ActionRates  
from Performance_Actions pa
INNER JOIN Performance_Impressions pi
ON pa.Alternative = Pi.Alternative
GROUP BY pa.[type];

The above query generates an error:

Lookup Error - SQL Server Database Error: Arithmetic overflow error converting expression to data type int.

Both pa.Actions_Logged and pi.Impressions_Served are int and the result would be a decimal. Is this the problem?

The Tables are pi:

Date       Alternative  Impressions_Served
05/04/2015      x           544432
05/04/2015      x           545990
18/04/2015      y           343325
06/04/2015      z           591316
06/04/2015      y                2

pa:

Date       Alternative  Actions_Logged  Type
05/04/2015     x            33         landing
01/04/2015     y             3         conversion
06/04/2015     k             8         landing
01/04/2015     x             3         conversion
18/04/2015     y             3         landing

Upvotes: 2

Views: 45

Answers (2)

Roman Marusyk
Roman Marusyk

Reputation: 24609

Type of expression in SUM determines return type.

Try the following:

SELECT pa.[type], (SUM(CAST(pa.Actions_Logged as BIGINT ))/SUM(CAST(pi.Impressions_Served as BIGINT ))) AS ActionRates  
from Performance_Actions pa
INNER JOIN Performance_Impressions pi
ON pa.Alternative = Pi.Alternative
GROUP BY pa.[type];

Upvotes: 1

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

Try this query

SELECT pa.[type], (isnull( SUM(pa.Actions_Logged),0)/isnull(SUM(pi.Impressions_Served),0)) AS ActionRates  
from Performance_Actions pa
INNER JOIN Performance_Impressions pi
ON pa.Alternative = Pi.Alternative
GROUP BY pa.[type];

Upvotes: 0

Related Questions