Как составить такой sql запрос?

Question

[suhuxa1]

Привет!
Есть 2 таблицы. Одна с доходами, вторая с расходами. Общего в них только даты. Структура у обоих такая:
Доход:
id | date | income | summa
Расход:
id | date | outcome | summa

Задача: Вывести на экран по дням доходы\расходы. Т.е такую таблицу:
07-04-2018 - Доход: 800, Расход: 500
08-04-2019 - Доход: 900, Расход: 600

И тд.

ВАЖНО: В таблицах у каждого дня может быть много записей. То есть, в таблицах Доход\Расход уже не сумарное значение дается, а множество мелких, а мне как раз сумарные надо вывести.

Я написал такой код, но что-то выводится фигня. Во-первых, только 1 дата, во-вторых, итоговые значения какие-то большие. Например, у меня за 1 апреля лишь 3 дохода было: 100 рублей, 300 и 500. А итоговый доход за 1 апреля мне вылез в 4 тысячи. Почему - не знаю. Надеюсь поможете исправить код, спасибо!

SELECT p.date, SUM(p.income) as inc, SUM(c.summa) as total 
FROM `payments` as p 
LEFT JOIN cash as c 
ON p.date = c.date

p.income (поле с доходами), c.summa (тут у нас все расходы)
Даты одинаковые, формата: 01-04-2018

Comments

[Game Master]

Привет
Как прокомментировали выше, суммы расходов и доходов желательно хранить в одной таблице, можно той же структуры:
id | date | income | summa
* Для сумм доходов income = 1, для расходов income = 0

Далее уже дело техники составить простой SQL запрос.

А так Вам уже дали 2 варианта по сути аналогичных решений через union all, что и объединяет результаты запросов из 2-х таблиц в одну, это и наводит к мысли, что нет смысла хранить данные в разных таблицах, но все зависит от архитектуры ПО.

[suhuxa1]

Sauran Baitarakhov, увы, не я проектировал, а так ответ от Andrey Ivanov очень помог)

Answer 1

[mletov]

SELECT t1.date,
	   t2.income,
	   t3.outcome
FROM
(
	SELECT date 
	FROM payments
	UNION
	SELECT date
	FROM cash	
) AS t1
LEFT JOIN
(
	SELECT date,
	       SUM(income) AS income
	FROM payments	   
	GROUP BY date
) AS t2
ON t1.date=t2.date
LEFT JOIN
(
	SELECT date,
	       SUM(summa) AS outcome
	FROM payments	   
	GROUP BY date
) AS t3
ON t1.date=t3.date

Comments

[Андрей]

На данный момент это единственно верный ответ. Остальные будут давать кратные размеры. Потому что если дат как в таблице прихода так и в таблице выхода может быть несколько - то суммировать надо заранее в подзапросах(можно конечно ещё хитрей - но это сложновато). Да, ещё непонятно всё таки что надо брать данные из income/outcome или из поля summa.

Answer 2

[Andrey bb]

А колонки income и outcome - это зачем?

Как-то так:

select date
	, sum( case when ppcc.dt = 1 then ppcc.sum else 0 end )
	, sum( case when ppcc.dt = 0 then ppcc.sum else 0 end )	
from (
	select
		pp.date		as date
		, 1			as dt
		, pp.sum 	as sum
	from payments pp
	union all
	select
		pp.date		as date
		, 0			as dt
		, pp.sum 	as sum
	from cash cc
	) ppcc
group by ppcc.date

зы. Было бы правильно всё хранить в одной таблице, тогда бы и вопрос и не возникал.

Answer 3

[Maks Codov]

добавьте group by p.date

Comments

[suhuxa1]

Так отлично все выводится, НО: по какой-то причине теперь сумма доходов из первой колонки умножается на количество записей о расходов из второй. Как-то это можно исправить? Простой пример:
Доход за 2 апреля - 300 рублей. Показывает 900. А дело в том, что в тот день есть аж 3 записи о расходах. А в те дни, где запись одна, или расходов вообще нет - считает отлично

[Maks Codov]

suhuxa1, был бы дамп заполненных таблиц, мог бы попробовать сам запрос

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

Answer 4

[d-stream]

select 
date,
sum(dbt),
sum(crd)
from(
select date, summa as dbt, 0 as crd  from t1
union all
select date, 0 as dbt, summa as crd from t2
) as t3 
group by date