AggregateOfMixedDataTypes error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

p-rat - 28 Feb 2008 17:44 GMT

I have an expression that returns Invoice amounts for the current
month. I try to Sum this though and get the aggregate of mixed data
types error. I've tried to convert the dates, but then get the 'trying
to Sum on data types other than numeric'. Can anyone help me figure
out how I can sum this column? Thank you. This is in Visual Studio on
SQL Server.

= iif(month(InvoiceDate)= month(getdate()) AND year(InvoiceDate)=
year(getdate()), InvoiceAmount, 0)

Reply to this Message

Roy Harvey (SQL Server MVP) - 28 Feb 2008 22:56 GMT

>I have an expression that returns Invoice amounts for the current
>month. I try to Sum this though and get the aggregate of mixed data
[quoted text clipped - 5 lines]
>= iif(month(InvoiceDate)= month(getdate()) AND year(InvoiceDate)=
>year(getdate()), InvoiceAmount, 0)

This looks like a mix of Access (iif) and SQL Server (getdate()). You
might want to explain that, and show the entire SELECT with the SUM.

In SQL Server I would write this:

CASE WHEN month(invoice_date) = month(getdate())
AND year(invoice_date) = year(getdate())
THEN InvoiceAmount
ELSE 0
END

Note that they type of the constant 0 has to be compatible with the
type of the column InvoiceAmount, and InvoiceAmount has to be a number
that can be SUMmed.

Roy Harvey
Beacon Falls, CT

Reply to this Message