Как в одной таблице по разным признакам в столбце вычесть сумма одних значений из других?

Question

[maksam07]

Добрый день. Не знаю как правильно задать вопрос, но лучше попробую рассказать что есть:

Предположим есть таблица:

CREATE TABLE IF NOT EXISTS tablename(
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `type` int(11) NOT NULL,
    `sum` varchar(50) NOT NULL default '0',
    PRIMARY KEY ( `id` )	
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

Это таблица приходов/расходов денежных средств.
type=0 - приход, type=1 - расход
sum - сумма прихода/расхода

Есть 10 строк, 5 приходов и 5 расходов.
Можно ли одним запросом получить результат суммирования всех приходов(между приходами), расходов(между расходами) и вычесть приходы из расходов? То есть получить как бы "баланс".

Answer 1

[Slava Rozhnev]

Вот возможное решение:

select
	sum(if(`type` = 0, `sum`, 0)) `income`,
	sum(if(`type` = 1, `sum`, 0)) `outcome`,
	sum(if(`type` = 0, `sum`, -`sum`)) `balance`
from tablename;

Test SQL online

Comments

[maksam07]

Это просто какая-то магия. Я примерно понимаю, как это работает, хотя понятно все написано (просто с if почти не работал), но это удивительно.
Подскажите, если знаете, таким образом "баланс" можно выводить на странице (при условии правильной индексации столбцов) или лучше баланс хранить в другой таблице, а это таблица будет просто как "история операций"?

[Slava Rozhnev]

Про правильной индексации этого достаточно. Хранение баланса в отдельной таблице возможно, но требует синхронизации данных (каждая операция должна обновлять баланс )

[maksam07]

Slava Rozhnev, да, за синхронизацию тоже думал. Но раз индексация должна спасти ситуацию, то попробую реализовать вариант с одной таблицей, возможно добавлю фейковых несколько сотен тысяч записей и посмотрю как запросы будут вести себя. Предварительно спасибо, но я сюда еще скоро вернусь :)

[maksam07]

Подскажите, можно 3-й sum заменить на что-то наподобие: `income`-`outcome` as `balance` ?
Ведь мы в первых двух посчитали что нам нужно и осталось только вычесть одно из другого, а выходит что для 3-го нужно писать выражение полностью.
Спрашиваю, потому что структура таблицы немного сложнее на самом деле. У меня получилось примерно такое:

SELECT
    sum(
        if(bids.type IN (0) AND bids.status IN ('success'), bids.sum, 0)
    ) as `income`,
    sum(
        if(bids.type IN (1,2) AND bids.status IN ('realpay', 'success', 'verify'), bids.sum, 0)
    ) as `outcome`,
    sum(
        CASE
            WHEN bids.type IN (0) AND bids.status IN ('success')
                THEN bids.sum
            WHEN bids.type IN (1, 2) AND bids.status IN ('realpay', 'success', 'verify')
                THEN -bids.sum
            ELSE 0
        END
    ) as `balance`
FROM ...

[Slava Rozhnev]

можно:

select `income`, `outcome`, (`income` - `outcome`) `balance`
from (
  select
	sum(if(`type` = 0, `sum`, 0)) `income`,
	sum(if(`type` = 1, `sum`, 0)) `outcome`
  from tablename
) `data`;

fiddle

[Slava Rozhnev]

with `data` as (
  select
	sum(if(`type` = 0, `sum`, 0)) `income`,
	sum(if(`type` = 1, `sum`, 0)) `outcome`
  from tablename
) select 
	`income`, `outcome`, (`income` - `outcome`) `balance` 
	from `data`;

[maksam07]

Slava Rozhnev, использовал первый вариант (из двух последних предложенных) + обернул sum в IFNULL чтобы получать 0, если строк вообще нет и пока что все кажется очень даже красивым. Позже сделаю тесты, чтобы правильно настроить индексацию и надеюсь что будет работать отлично. Огромная благодарность в решении этого вопроса

Answer 2

[Олег]

можно sum агрегатная функция может работать и по выражениям.
https://metanit.com/sql/mysql/6.4.php
класика case есть во всех диалектах SQL
if - упрошенный вариант в муське и ряде других

Получается нам нужно посчитать для строк подходящих под условия сумму выражения
амоунт по операции умножит на 1 или -1.
p.s.
поле в базе лучше не называть именами ключевых слов или функций
иначе все времы в кавычки брать придется. это я про имя поля sum

Comments

[maksam07]

нашел один вариант с (сум+case)-(сум+case),спасибо, но сейчас подумываю о том, чтобы завести отдельную таблицу "балансов", чтобы там хранились итоговые значения. А то боюсь что в таком виде может пойти что-то не так

[Олег]

Максим Компаниец, почитайте еще про coalesce .
арифметические выражения где аргумент может быть нулл вернут тоже нулл.
Это если данные могут быть плохими. Мало приятного словить вместо суммы такую бяку.

[maksam07]

Олег, подсказали еще один вариант (другой комментарий), сейчас он кажется чуть более привлекательным, попробую на нем опыты провести, если вдруг будут неудачные, то буду думать дальше