Расчет общей суммы стоимости в MySQL
У меня есть таблица базы данных топливо, в которой есть поле fID (первичный, автоинкремент), vID (для идентификатора транспортного средства), объем (для количества топлива в литрах), цена (для стоимости топлива за литр), метр (для текущего одометр транспортного средства на дату заправки), дата (дата заправки транспортного средства), vendorID (для поставщика топлива или только место заправки транспортного средства), примечания (любые дополнительные примечания к транспортному средству или топливу) и тип (марка топлива).
Я хочу сделать запрос, который рассчитал бы стоимость топлива на каждую заправку, умножив объем (купленные литры) на цену (стоимость за литр), затем добавив общую сумму для всех топливных записей для конкретного автомобиля, а затем, наконец, добавив стоимость все записи о топливе в таблице (fID уникален, но vID повторяется (поскольку автомобиль будет заполняться время от времени).
Это то, что я использую сейчас, но он возвращает только стоимость каждой заправки (без суммирования других заправок).
Select
date_format(f1.date, '%y-%m-%y %H:%i:%s) as date,
f1.meter as mileage,
case when f2.meter is null then 0
else f1.meter - f2.meter
end as distance
f1.fID,
f1.volume,
f1.volume * f1.price as cost
from
fuel f1
left outer join
fuel f2
On
f2.date = (
select
max(date)
from
fuel
where
fuel.vID = f1.vID
and
fuel.date < f1.date
)
where
f1.ID = ?
Order by f1.date
Любая помощь будет принята с благодарностью.
Ответы 1
Вам нужно только незначительно изменить свой запрос, удалить предложение WHERE и добавить GROUP BY:
SELECT f1.vid,
DATE_FORMAT(f1.date, '%y-%m-%d %H:%i:%s') AS date,
f1.meter AS mileage,
CASE WHEN f2.meter IS NULL THEN 0
ELSE f1.meter - f2.meter
END AS distance,
f1.volume,
SUM(f1.volume * f1.price) AS cost
FROM fuel f1
LEFT JOIN fuel f2
ON f2.date = (SELECT MAX(date)
FROM fuel
WHERE fuel.vID = f1.vID AND fuel.date < f1.date)
GROUP BY f1.vid, f1.date WITH ROLLUP
Для образцов данных из вашего предыдущий вопрос это дает следующий результат. Вы можете определить итоговые строки для каждого транспортного средства по null в столбце date и общую итоговую строку по null в столбце vid.
vid date mileage distance volume cost
26 18-05-27 05:57:00 4500 0 25 18750
26 18-05-27 05:58:00 6000 1500 20 15000
26 (null) 6000 1500 20 33750
27 18-05-27 04:58:00 1200 0 15 18000
27 18-05-27 05:50:00 2000 800 5 5000
27 (null) 2000 800 5 23000
28 18-05-27 05:53:00 5000 0 15 12000
28 (null) 5000 0 15 12000
(null) (null) 5000 0 15 68750
Если вас не интересует просмотр отдельных записей для каждого транспортного средства, вы можете удалить дату из GROUP BY (и переменные, относящиеся к дате, из выбора). Этот запрос также суммирует расстояние и объем:
SELECT f1.vid,
SUM(CASE WHEN f2.meter IS NULL THEN 0
ELSE f1.meter - f2.meter
END) AS distance,
SUM(f1.volume) as volume,
SUM(f1.volume * f1.price) AS cost
FROM fuel f1
LEFT JOIN fuel f2
ON f2.date = (SELECT MAX(date)
FROM fuel
WHERE fuel.vID = f1.vID AND fuel.date < f1.date)
GROUP BY f1.vid WITH ROLLUP
Вывод (в итоговой строке есть null для vid):
vid distance volume cost
26 1500 45 33750
27 800 20 23000
28 0 15 12000
(null) 2300 80 68750
Это именно то, что я хотел. Итак, как я могу получить эту итоговую строку (Null vid) для отображения в представлении в html?
Другое дело, как я могу дополнительно изменить запрос, чтобы он возвращал итоги (сводку) за любой период времени ... Скажите, хочу ли я сводку общей стоимости топлива за последний месяц или прошлую неделю или последние 20 дней, как я могу заставить это работать?
В первом запросе есть несколько сомнительная функциональная зависимость, поэтому второй запрос кажется более подходящим.
@Kingsley, итоговая строка будет последней строкой, которую вы получите, если вы зациклите fetch () в PHP. Чтобы получить результаты за определенный период времени, используйте что-то вроде WHERE f1.date >= CURDATE() - INTERVAL 20 DAY непосредственно перед предложением GROUP BY. демонстрация
@ Strawberry, о чем вы говорите?
У вас были образцы данных, но вы не поделились? Бу!