CODEWITHSUNDEEP
sqlt-sqlsqlite
Andre Reese
Andre Reese

Reputation: 1

Sqlite query problem

I am having trouble making a query that aims to get the percentage of variation in sales between 2 months.

I have an example in SQL Server that works correctly.

Example:

CREATE TABLE TAB1 ( SELLMONTH1 INT
                  , SELLMONTH2 INT );

INSERT INTO TAB1 VALUES (1000,1250);

SELECT convert(decimal(10,2)
     , (convert(float,(SELLMONTH2 - SELLMONTH1)) / SELLMONTH1) * 100)
FROM TAB1;

Returns me 25.00

How to do that same query in SQLite?

I've tried various ways but always returns 0

Thanks in advance

Upvotes: 0

Views: 294

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115520

Try this:

SELECT (SELLMONTH2 - SELLMONTH1) * 100.0 / SELLMONTH1
FROM TAB1;

If you specificaly want to cast them as floats (or numeric or whatever), don't use CONVERT but CAST instead:

CAST( sellmonth AS float )

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453028

Try to CAST one of them as FLOAT to avoid integer division (From this thread)

Upvotes: 2

Related Questions