当前位置: 动力学知识库 > 问答 > 编程问答 >

Is MySQL showing wrong percentage?

问题描述:

Im currently trying to get the percentage difference from the same column, but with different dates.

This is the query I'm currently testing:

SELECT curr.slug,

curr.senaste * 100 / NULLIF(prev.senaste, 0) as percentage

FROM aktier AS curr

INNER JOIN aktier AS prev

ON curr.slug = prev.slug

WHERE date(prev.timestamp) = '2015-11-16'

AND date(curr.timestamp) = CURDATE()

AND curr.slug = 'abb-ltd'

If i run this query i will get the result: 103.14465408805032

But, if I run query SELECT senaste FROM aktier WHERE slug = 'abb-ltd'i get theses two values:

159,4 164,1

And when i run 1-(159,4/164,1) on my calculator i will get this result: 0,2864.

Whats wrong with the current set up? And: how do i get the result from the query to show 2,9%?

The column senaste is a varchar(5) with utf8_unicode_ci.

SQL-fiddle: http://sqlfiddle.com/#!9/8a859

网友答案:

Your column senaste is a VARCHAR(5) containing data like '159,4'. VARCHAR fields should not be used to store numbers, especially if you intend on having MySQL run math on the values.

You should instead be using a DECIMAL(10,1) (assuming you only want a single decimal digit) and you should be using a period, not a comma in your number format. (eg, '159.4')

分享给朋友:
您可能感兴趣的文章:
随机阅读: