SQL-запрос для подсчета итогов

Я новичок в SQL-запросах, поэтому, пожалуйста, полегче со мной, если то, что я сделал до сих пор, действительно странно :)

У меня есть две таблицы - A для доходов и B для расходов:

Business_ID Income_Desc Income_Amount
1           Income A    1000
1           Income B    3000
1           Income C    2000

Business_ID Expen_Amount
1           2500

Я хотел бы создать таблицу, которая показывает каждую из сумм доходов, одну сумму расходов, общий доход, общие расходы и общую сумму общих доходов и расходов.

Что-то вроде этого, если можно

Business_ID Income Description  Income Amount   Expenditure Amount  Total
1           Income A            1000            2500                 -
1           Income B            3000            -                    -
1           Income C            2000            -                    -
1           All Amounts         6000            2500                  3500

Это то, что я пробовал до сих пор

SELECT a. Business_ID, COALESCE (a.Income_Desc, 'All Amounts') AS 'Income Description', SUM(a.Income_Amount) AS 'Income Amount', SUM(b.Expen_Amount) AS Expenditure Amount', (sum(a.Income_Amount)-SUM(b.Expen_Amount)) AS 'Total'
FROM Income AS a LEFT JOIN Expenditure AS b ON a.Business_ID = b. Business_ID
GROUP BY a. Business_ID,  a.Income_Desc WITH ROLLUP

Результат, который я получаю, это

Business_ID Income Description  Income Amount   Expenditure Amount  Total
1           Income A            1000            2500                 -1500
1           Income B            3000            2500                 500
1           Income C            2000            2500                 -500
1           All Amounts         6000            7500                 -1500
            All Amounts         6000            7500                 -1500

Можно ли получить вывод, подобный тому, который я предоставил выше? Не могли бы вы показать мне, как этого добиться (или что-то очень близкое), пожалуйста?

Спасибо


sql totals
person assetman    schedule 15.01.2020    source источник
comment
Какая у вас версия mysql?   -  person P.Salmon    schedule 15.01.2020
comment
В вашей модели данных есть некоторые проблемы, потому что неясно, какой записи о доходах соответствует сумма расходов в одной записи. Вместо того, чтобы пытаться составить запрос, вы можете реорганизовать дизайн своей таблицы.   -  person Tim Biegeleisen    schedule 15.01.2020
comment
Привет, это 10.3.14-MariaDB-log   -  person assetman    schedule 15.01.2020
comment
Пометка @assetman как mysql может привлечь ответы, которые могут не подходить для mariadb, поскольку они различаются с точки зрения доступных функций и развиваются с разной скоростью.   -  person P.Salmon    schedule 15.01.2020
comment
@P.Salmon, спасибо за совет. Я постараюсь удалить тег MySQL, если это возможно   -  person assetman    schedule 15.01.2020

Ответы (2)


arrow_upward
0
arrow_downward

Вы можете использовать row_number() для join:

with ie as (
      select i.business_id, i.income_desc, i.income_amount,
             e.expen_amount
      from (select i.*,
                   row_number() over (partition by business_id order by income_desc) as seqnum
            from income i
           ) i left join
           (select e.*,
                   row_number() over (partition by business_id order by expen_amount) as seqnum
            from expenditure e
           ) e
           on i.business_id = e.business_id and i.seqnum = e.seqnum
       )
select ie.*
from ie
union all
select business_id, 'Total', sum(income_amount), sum(expen_amount)
from ie
group by business_id;
person Gordon Linoff    schedule 15.01.2020
comment
Спасибо за ответ. Я не знаком с этой функцией, поэтому мне нужно немного прочитать о ней :) - person assetman; 15.01.2020

arrow_upward
0
arrow_downward

Вы можете сделать подзапрос из исходного запроса и выбрать только те значения, для которых бизнес-идентификатор не равен нулю. Кроме того, используйте CASE WHEN для идентификации этих значений ‹ 0 и замените их на «-»:

SELECT x.Business_ID
     , x.`Income Description`
     , x.`Income Amount`
     , x.`Expenditure Amount`
     , x.Total
FROM
(SELECT a. Business_ID
     , COALESCE (a.Income_Desc, 'All Amounts') AS 'Income Description'
     , SUM(a.Income_Amount) AS 'Income Amount'
     , SUM(b.Expen_Amount) AS 'Expenditure Amount'
     , CASE WHEN (sum(a.Income_Amount)-SUM(b.Expen_Amount)) < 0
       THEN '-'
       ELSE (sum(a.Income_Amount)-SUM(b.Expen_Amount))
       END AS 'Total'
FROM Income AS a 
    LEFT JOIN Expenditure AS b ON a.Business_ID = b.Business_ID
WHERE a.Business_ID is not null and b.Business_ID is not null
GROUP BY a.Business_ID, a.Income_Desc WITH ROLLUP) as x
where x.Business_ID is not null

Скрипт БД

person waka    schedule 15.01.2020
comment
Вака, спасибо за ответ и за ссылку на Fiddle. Мне нравится использование CASE WHEN. Когда я выполнил запрос в Fiddle, результат дает общую сумму расходов 7500. Можно ли указать только одну цифру расходов 2500 как общую сумму по всем суммам? - person assetman; 15.01.2020