February 23, 2015 at 10:45 am
New to this all and cant figure out how to get around this error with a Group By Roll up.
Error ~~ Msg 8117, Level 16, State 1, Line 17
Operand data type bit is invalid for sum operator.
SELECT ResponseAppeal.AppealID AS "Response ID", ResponseAppeal.AppealName AS "Response Name", dbo.v_AppealGroupingOptions.[Ack-Letter-code] AS "Acknowledgment WordPerfect Code",
COUNT(OutboundActivity.Pulled) AS "Number of Printed Letters",
CAST( OutboundActivity.DatePulled as Date) AS "Date Pulled"
FROM OutboundActivity WITH(NOLOCK)
LEFT OUTER JOIN Appeals ResponseAppeal WITH(NOLOCK)
ON OutboundActivity.ResponseID = ResponseAppeal.UniqueID
LEFT OUTER JOIN dbo.v_AppealGroupingOptions (nolock)
ON OutboundActivity.ResponseID = dbo.v_AppealGroupingOptions.appealid
LEFT OUTER JOIN Appeals WITH(NOLOCK)
ON OutboundActivity.AppealID = Appeals.UniqueID
WHERE (CAST( OutboundActivity.DatePulled as Date) >= '01/05/2015'
AND CAST( OutboundActivity.DatePulled as Date) < '01/06/2015')
GROUP BY ResponseAppeal.AppealID, ResponseAppeal.AppealName, dbo.v_AppealGroupingOptions.[Ack-Letter-code],
CAST( OutboundActivity.DatePulled as Date), Appeals.AppealID
ORDER BY dbo.v_AppealGroupingOptions.[Ack-Letter-code] ASC, Appeals.AppealID ASC
Select OutboundActivity.Pulled, SUM (OutboundActivity.Pulled)
From OutboundActivity
Group by
ROLLUP((Pulled))
February 23, 2015 at 11:01 am
Try
SUM(Cast [bitcolumn] as int)
Instead.
February 23, 2015 at 11:01 am
That has nothing to do with the rollup. Even if it's considered as a numeric data type, bit data cannot be used in a SUM(), you need to convert it to a different data type such as int.
You're also grouping by the same column that you're using in your aggregate function. That will only give you incorrect or useless information.
February 23, 2015 at 12:11 pm
What would be the best way to sum up this column.
(OutboundActivity.Pulled) AS "Number of Printed Letters",
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply