Используя SQL, как правильно суммировать столбцы из нескольких таблиц в общие группы?

То, что вы получаете, является декартовым произведением. Вы можете увидеть последствия этого, удалив группировку и просмотрев данные.

Например; если ваша зеленая таблица содержит 2 строки, а синяя таблица — 4, ваше соединение вернет в общей сложности 8 записей.

Чтобы решить проблему, ну, вы почти у цели. У вас есть все нужные части, просто вы не совсем правильно их собрали.

Предположим, что следующий запрос возвращает правильные результаты для зеленого цвета:

SELECT CategoryID
     , Sum(GreenValue) As GreenTotal
FROM   Green
GROUP
    BY CategoryID

Результаты для синего можно получить, следуя тому же методу:

SELECT CategoryID
     , Sum(BueValue) As BlueTotal
FROM   Blue
GROUP
    BY CategoryID

Теперь, когда у нас есть два различных результата, которые являются правильными, мы должны соединить эти результаты с нашей таблицей категорий:

SELECT Category.CategoryName
     , GreenSummary.GreenTotal
     , BlueSummary.BlueTotal
FROM   Category
 LEFT
  JOIN (    
        SELECT CategoryID
             , Sum(GreenValue) As GreenTotal
        FROM   Green
        GROUP
            BY CategoryID
       ) As GreenSummary
    ON GreenSummary.CategoryID = Category.CategoryID
 LEFT
  JOIN (    
        SELECT CategoryID
             , Sum(BlueValue) As BlueTotal
        FROM   Blue
        GROUP
            BY CategoryID
       ) As BlueSummary
    ON BlueSummary.CategoryID = Category.CategoryID

На мой взгляд, что-то подобное лучше всего сделать с помощью APPLY. Быстрая с точки зрения производительности, простая в использовании и легкая в управлении в случае изменений в запросе.

IE:

SELECT C.[CategoryName], G.[GreenTotal], B.[BlueTotal]
FROM [Category] C
OUTER APPLY (SELECT SUM([GreenValue]) AS [GreenTotal] FROM [Green] WHERE [CategoryID] = C.CategoryID) G
OUTER APPLY (SELECT SUM([BlueValue]) AS [BlueTotal] FROM [Blue] WHERE [CategoryID] = C.CategoryID) B

Создайте запрос для каждой итоговой таблицы. Сгруппируйте по категориям, создайте столбец суммы и добавьте идентификатор столбца.

Затем используйте эти запросы в качестве подзапроса и выполните левое внешнее соединение с основной таблицей. Это может дать вам ожидаемый результат и может иметь нулевые значения, когда сумма недоступна. Вы можете использовать функцию isnull для преобразования нулевых значений в 0.

Я бы суммировал их сначала с CTE. Затем просто соедините 2 вместе в том, что является общим, только с одним вхождением в каждом, CategoryName. Таким образом, вы не можете получить декартово произведение. Я вставил оператор isnull, потому что есть вероятность, что нет результатов для CategoryName, выделенного синим или зеленым цветом. Если вы этого не сделаете, вы можете получить null для своего CategoryName.

WITH GREENSUM as (
SELECT
    [Category].[CategoryName],
    SUM([Green].[GreenValue]) AS [GreenTotal]
FROM
    [Category]
LEFT JOIN
    [Green] ON [Category].[CategoryID] = [Green].[CategoryID]
GROUP BY
    [Category].[CategoryName]
),
WITH BLUESUM as (
SELECT
    [Category].[CategoryName],
    SUM([Blue].[BlueValue]) AS [BlueTotal]
FROM
    [Category]
LEFT JOIN
    [Blue] ON [Category].[CategoryID] = [Blue].[CategoryID]
GROUP BY
    [Category].[CategoryName])
SELECT isnull(GREENSUM.CategoryName, BLUESUM.CategoryName) as CategoryName, 
    GreenTotal, BlueTotal 
FROM [GREENSUM] 
FULL OUTER JOIN 
    [BLUESUM] ON [GREENSUM].CategoryName = [BLUESUM].CategoryName)

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

/*
create table Category ( CategoryId Integer, CategoryName nvarchar(50) )
create table Green ( CategoryId Integer, GreenValue Integer )
create table Blue ( CategoryId Integer, BlueValue Integer )

insert into Category VALUES (1,'Square'),(2,'Circle')
insert into Blue VALUES (1,10),(1,20),(2,5),(2,9),(1,12),(1,21),(2,4),(2,8)
insert into Green VALUES (1,3),(1,6),(2,2),(2,7),(1,2),(1,5),(2,4),(2,8)
*/

with CatSums(ColorRank, CategoryId, CategoryValue) as
(
  select 1, CategoryId, GreenValue from Green
  union all
  select 2, CategoryId, BlueValue  from Blue
)

select 
    C.CategoryName, 
    Sum(case when ColorRank = 1 then CategoryValue else 0 end) as GreenTotal,
    Sum(case when ColorRank = 2 then CategoryValue else 0 end) as BlueTotal
 from CatSums S left join Category C on C.CategoryId = S.CategoryId
group by C.CategoryName

• Хотя я должен признать, что мне все больше нравится решение OUTER APPLY.