Casting a expression result on SQLite, from float to decimal

Question

I have the following expression on a query that I'm running against a SQLite database, using SQLite.NET.

SUM(Detail.Qty * Products.Weight) AS MovedWeight

The query works, but as Detail.Qty is decimal, and Weight is decimal too, it returns a float number. I'm trying to cast it to something like DECIMAL(10,2). I know SQLite is special about datatypes, and I did try to use CAST with no sucess.

CAST(SUM(Detail.Qty * Products.Weight) AS DECIMAL(10,2)) AS MovedWeight

It works too, but takes no notice about the precision (10,2), and gives me the same result that the first example.

Anyone has ideas? Converting the result to decimal in the DataTable would be acceptable too, but I have no idea of how to do that without an ugly, resource-consuming loop (it's expected that the query, at some point, will return 3000 or more results).

Answer 1

The ROUND() function does the trick.

ROUND(SUM(Detail.Qty * Products.Weight), 2) AS MovedWeight

Answer 2

In SQLite, the numbers in parentheses after a type name don't do anything; they're just allowed for compatibility with other databases. VARCHAR(100) means nothing more than "text affinity" and DECIMAL(10, 2) means nothing more than "numeric affinity".