CODEWITHSUNDEEP
phpmysql
CloudDev
CloudDev

Reputation: 5

PHP count values with same id

My current project: I'm trying to create an overview of all cars in a company.

I get the following mysql result table:

|car_id|distance|
|   1  |  33875 |
|   1  |  33027 |
|   1  |  31579 |
|   2  | 125636 |
|   2  | 124937 |

The "Distance" is the mileage after a fill up.

What do I need?

I need the total distance for each car.

The result should look like:

|car_id|distance|
|   1  |  2296  | (33875 - 31579)
|   2  |   699  | (125636 - 124937)

Upvotes: 0

Views: 104

Answers (2)

user2417483
user2417483

Reputation:

Adding to Darshan's answer:

select id, (max(distance) - min(distance)) as mileage
, concat('(',min(distance),' - ',max(distance),')') as distance
from cars
group by id;

Upvotes: 0

Darshan Mehta
Darshan Mehta

Reputation: 30809

You can easily do it with group by clause, e.g.:

SELECT id, (MAX(distance) - MIN(distance)) AS mileage
FROM cars
GROUP BY id;

Here's the SQL Fiddle.

Upvotes: 3

Related Questions