user1022585
user1022585

Reputation: 13661

mysql maths based query

I have a table of firearms in my game, it has 3 fields:

Name       dmg    timer
------------------------
Pistol     5      30
Shotgun    10     45
Sniper     16     60

The timer represents how often in seconds it can be shot. Theres a lot more rows than this, but..

I want to pull them in order of rate of fire, which one can damage the most every minute. So the pistol would do 10 damage every minute, the shotgun 12.5, the sniper 16.

How would I do this as a query?

SELECT * FROM firearms ORDER BY ?

It's probably some basic math but I'm not thinking clearly.

Upvotes: 1

Views: 161

Answers (5)

Liam Dawson
Liam Dawson

Reputation: 1199

SELECT name, (dmg/timer) FROM Weapons ORDER BY (dmg/timer)

returns all the weapons in the database by their DPS (damage per second), with two columns: the name, and the DPS.

Upvotes: 0

Oleg Dok
Oleg Dok

Reputation: 21776

SELECT 
  dmg*60.0/timer AS DamagePerMinute,
  *
FROM firearms 
ORDER BY 1 DESC

Upvotes: 0

John Woo
John Woo

Reputation: 263943

SELECT Name,
    dmg,
    timer, 
    (dmg * (60 / timer)) as  
FROM firearms 
ORDER BY xx desc 

Upvotes: 0

user359040
user359040

Reputation:

Try:

SELECT * FROM firearms ORDER BY dmg/timer desc

- to get most damaging first.

Upvotes: 0

Paul Bain
Paul Bain

Reputation: 4397

You just need to calculate a weighted damage, I think something like this would do:

SELECT * FROM firearms ORDER BY (dmg * (60/timer)) desc

Upvotes: 1

Related Questions