SUM двумя разными группами GROUP BY

Я получаю неправильный результат от моего отчета. Может быть, я пропустил что-то простое.

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

Проблема: разные запасные части в магазине (можно назвать SP ) могут быть связаны с одной и той же запасной частью в «ремонтной таблице» ( TSP ). Мне нужно движение товаров каждого запасного пакета в SP и счетчик претензий каждого отдельного запасника в TSP .

Это очень упрощенная выдержка из соответствующей части:

 create table #tsp(id int, name varchar(20),claimed int); create table #sp(id int, name varchar(20),fiTsp int,ordered int); insert into #tsp values(1,'1235-6044',300); insert into #tsp values(2,'1234-5678',400); insert into #sp values(1,'1235-6044',1,30); insert into #sp values(2,'1235-6044',1,40); insert into #sp values(3,'1235-6044',1,50); insert into #sp values(4,'1234-5678',2,60); WITH cte AS( select tsp.id As TspID,tsp.name as TspName,tsp.claimed As Claimed ,sp.id As SpID,sp.name As SpName,sp.ordered As Ordered from #sp sp inner join #tsp tsp on sp.fiTsp=tsp.id ) SELECT TspName, SUM(Claimed) As Claimed, Sum(Ordered) As Ordered FROM cte Group By TspName drop table #tsp; drop table #sp; 

Результат:

 TspName Claimed Ordered 1234-5678 400 60 1235-6044 900 120 

Ordered -count правильный, но искомый -count должен быть 300 вместо 900 для TspName = '1235-6044'.

Мне нужно сгруппировать по Tsp.ID для номера заявки и группы по Sp.ID для счета заказа. Но как в одном запросе?

Edit : На самом деле TVF выглядит (обратите внимание, что getOrdered и getClaimed являются SVF и что я группирую внешний выбор в категории TSP):

 CREATE FUNCTION [Gambio].[rptReusedStatistics]( @fromDate datetime ,@toDate datetime ,@fromInvoiceDate datetime ,@toInvoiceDate datetime ,@idClaimStatus varchar(50) ,@idSparePartCategories varchar(1000) ,@idSpareParts varchar(1000) ) RETURNS TABLE AS RETURN( WITH ExclusionCat AS( SELECT idSparePartCategory AS ID From tabSparePartCategory WHERE idSparePartCategory IN(- 3, - 1, 6, 172,168) ), Report AS( SELECT Cat.SparePartCategoryName AS Category ,TSP.SparePartDescription AS Part ,TSP.SparePartName AS PartNumber ,SP.Inventory ,Gambio.getGoodsIn(SP.idSparePart,@FromDate,@ToDate) GoodsIn ,Gambio.getOrdered(SP.idSparePart,@FromDate,@ToDate) Ordered --,CASE WHEN TSP.idSparePart IS NULL THEN 0 ELSE -- Gambio.getClaimed(TSP.idSparePart,@FromInvoiceDate,@ToInvoiceDate,@idClaimStatus,NULL)END AS Claimed ,CASE WHEN TSP.idSparePart IS NULL THEN 0 ELSE Gambio.getClaimed(TSP.idSparePart,@FromInvoiceDate,@ToInvoiceDate,@idClaimStatus,1)END AS ClaimedReused ,CASE WHEN TSP.idSparePart IS NULL THEN 0 ELSE Gambio.getCostSaving(TSP.idSparePart,@FromInvoiceDate,@ToInvoiceDate,@idClaimStatus)END AS Costsaving FROM Gambio.SparePart AS SP INNER JOIN tabSparePart AS TSP ON SP.fiTabSparePart = TSP.idSparePart INNER JOIN tabSparePartCategory AS Cat ON Cat.idSparePartCategory=TSP.fiSparePartCategory WHERE Cat.idSparePartCategory NOT IN(SELECT ID FROM ExclusionCat) AND (@idSparePartCategories IS NULL OR TSP.fiSparePartCategory IN( SELECT Item From dbo.Split(@idSparePartCategories,',') ) ) AND (@idSpareParts IS NULL OR TSP.idSparePart IN( SELECT Item From dbo.Split(@idSpareParts,',') ) ) ) SELECT Category --, Part --, PartNumber , SUM(Inventory)As InventoryCount , SUM(GoodsIn) As GoodsIn , SUM(Ordered) As Ordered --, SUM(Claimed) As Claimed , SUM(ClaimedReused)AS ClaimedReused , SUM(Costsaving) As Costsaving , Count(*) AS PartCount FROM Report GROUP BY Category ) 

Решение :

Благодаря Aliostad я решил его с помощью первой группировки, а затем присоединился (фактический TVF, сведенный к минимуму):

 WITH Report AS( SELECT Cat.SparePartCategoryName AS Category ,TSP.SparePartDescription AS Part ,TSP.SparePartName AS PartNumber ,SP.Inventory ,SP.GoodsIn ,SP.Ordered ,Gambio.getClaimed(TSP.idSparePart,@FromInvoiceDate,@ToInvoiceDate,@idClaimStatus,1) AS ClaimedReused ,Gambio.getCostSaving(TSP.idSparePart,@FromInvoiceDate,@ToInvoiceDate,@idClaimStatus) AS Costsaving FROM ( SELECT GSP.fiTabSparePart ,SUM(GSP.Inventory)AS Inventory ,SUM(Gambio.getGoodsIn(GSP.idSparePart,@FromDate,@ToDate))AS GoodsIn ,SUM(Gambio.getOrdered(GSP.idSparePart,@FromDate,@ToDate))AS Ordered FROM Gambio.SparePart GSP GROUP BY GSP.fiTabSparePart )As SP INNER JOIN tabSparePart TSP ON SP.fiTabSparePart = TSP.idSparePart INNER JOIN tabSparePartCategory AS Cat ON Cat.idSparePartCategory=TSP.fiSparePartCategory ) SELECT Category , SUM(Inventory)As InventoryCount , SUM(GoodsIn) As GoodsIn , SUM(Ordered) As Ordered , SUM(ClaimedReused)AS ClaimedReused , SUM(Costsaving) As Costsaving , Count(*) AS PartCount FROM Report GROUP BY Category 

Сначала вы подключаетесь, а затем GROUPing by. Вам нужно сначала отменить его, GROUP BY, а затем ПРИСОЕДИНЯЙТЕСЬ.

Итак, здесь, в моем подзапросе, я сначала группирую, а затем присоединяюсь:

 select claimed, ordered from #tsp inner JOIN (select fitsp, SUM(ordered) as ordered from #sp group by fitsp) as SUMS on SUMS.fiTsp = id; 

Я думаю, вам просто нужно выбрать «Заявить» и добавить его в группу «В», чтобы получить то, что вы ищете.

 WITH cte AS( select tsp.id As TspID,tsp.name as TspName,tsp.claimed As Claimed ,sp.id As SpID,sp.name As SpName,sp.ordered As Ordered from #sp sp inner join #tsp tsp on sp.fiTsp=tsp.id ) SELECT TspName, Claimed, Sum(Ordered) As Ordered FROM cte Group By TspName, Claimed 

Ваш cte является внутренним соединением между tsp и sp , что означает, что данные, которые вы запрашиваете, выглядят следующим образом:

 SpID Ordered TspID TspName Claimed 1 30 1 1235-6044 300 2 40 1 1235-6044 300 3 50 1 1235-6044 300 4 60 2 1234-5678 400 

Обратите внимание, как TspID , TspName и Claimed . Группировка по TspName означает, что данные группируются в две группы: одна для 1235-6044 и одна для 1234-5678 . Первая группа имеет 3 строки, на которых выполняются агрегированные функции, вторая – только одна. Вот почему ваша sum(Claimed) получит 300 * 3 = 900.

Как предположил Aliostad, вы должны сначала сгруппировать TspID и выполнить сумму TspID а затем присоединиться к tsp .

Не нужно присоединяться, просто подберите:

 create table #tsp(id int, name varchar(20),claimed int); create table #sp(id int, name varchar(20),fiTsp int,ordered int); insert into #tsp values(1,'1235-6044',300); insert into #tsp values(2,'1234-5678',400); insert into #sp values(1,'1235-6044',1,30); insert into #sp values(2,'1235-6044',1,40); insert into #sp values(3,'1235-6044',1,50); insert into #sp values(4,'1234-5678',2,60); WITH cte AS( select tsp.id As TspID,tsp.name as TspName,tsp.claimed As Claimed ,sp.id As SpID,sp.name As SpName,sp.ordered As Ordered from #sp sp inner join #tsp tsp on sp.fiTsp=tsp.id ) SELECT id, name, SUM(claimed) as Claimed, (SELECT SUM(ordered) FROM #sp WHERE #sp.fiTsp = #tsp.id GROUP BY #sp.fiTsp) AS Ordered FROM #tsp GROUP BY id, name drop table #tsp; drop table #sp; 

Производит:

 id name Claimed Ordered 1 1235-6044 300 120 2 1234-5678 400 60 

— РЕДАКТИРОВАТЬ —

Основываясь на дополнительной информации, я пытаюсь разделить CTE, чтобы сформировать данные в соответствии с примером. Я полностью признаю, что подход Aliostad может дать более чистый запрос, но вот попытка (полностью слепая) с использованием подзапроса:

 CREATE FUNCTION [Gambio].[rptReusedStatistics]( @fromDate datetime ,@toDate datetime ,@fromInvoiceDate datetime ,@toInvoiceDate datetime ,@idClaimStatus varchar(50) ,@idSparePartCategories varchar(1000) ,@idSpareParts varchar(1000) ) RETURNS TABLE AS RETURN( WITH ExclusionCat AS ( SELECT idSparePartCategory AS ID From tabSparePartCategory WHERE idSparePartCategory IN(- 3, - 1, 6, 172,168) ), ReportSP AS ( SELECT fiTabSparePart ,Inventory ,Gambio.getGoodsIn(idSparePart,@FromDate,@ToDate) GoodsIn ,Gambio.getOrdered(idSparePart,@FromDate,@ToDate) Ordered FROM Gambio.SparePart ), ReportTSP AS ( SELECT TSP.idSparePart ,Cat.SparePartCategoryName AS Category ,TSP.SparePartDescription AS Part ,TSP.SparePartName AS PartNumber ,CASE WHEN TSP.idSparePart IS NULL THEN 0 ELSE Gambio.getClaimed(TSP.idSparePart,@FromInvoiceDate,@ToInvoiceDate,@idClaimStatus,1)END AS ClaimedReused ,CASE WHEN TSP.idSparePart IS NULL THEN 0 ELSE Gambio.getCostSaving(TSP.idSparePart,@FromInvoiceDate,@ToInvoiceDate,@idClaimStatus)END AS Costsaving FROM tabSparePart AS TSP INNER JOIN tabSparePartCategory AS Cat ON Cat.idSparePartCategory=TSP.fiSparePartCategory WHERE Cat.idSparePartCategory NOT IN(SELECT ID FROM ExclusionCat) AND (@idSparePartCategories IS NULL OR TSP.fiSparePartCategory IN( SELECT Item From dbo.Split(@idSparePartCategories,',') ) ) AND (@idSpareParts IS NULL OR TSP.idSparePart IN( SELECT Item From dbo.Split(@idSpareParts,',') ) ) ) SELECT Category --, Part --, PartNumber , (SELECT SUM(Inventory) FROM ReportSP WHERE ReportSP.fiTabSparePart = idSparePart GROUP BY fiTabSparePart) AS Inventory , (SELECT SUM(GoodsIn) FROM ReportSP WHERE ReportSP.fiTabSparePart = idSparePart GROUP BY fiTabSparePart) AS GoodsIn , (SELECT SUM(Ordered) FROM ReportSP WHERE ReportSP.fiTabSparePart = idSparePart GROUP BY fiTabSparePart) AS Ordered , Claimed , ClaimedReused , Costsaving , Count(*) AS PartCount FROM ReportTSP GROUP BY Category ) 

Без лучшего понимания всей схемы трудно охватить все возможные события, но будет ли это работать или нет (я подозреваю, что PartCount будет 1 для всех экземпляров), надеюсь, это даст вам несколько свежих мыслей о альтернативных подходах.

 SELECT tsp.name ,max(tsp.claimed) as claimed ,sum(sp.ordered) as ordered from #sp sp inner join #tsp tsp on sp.fiTsp=tsp.id GROUP BY tsp.name 
Interesting Posts

Как создать временную таблицу для динамического запроса, чтобы я мог BCP вывод

Разработка для кластера SQL Server

Ошибка входа в SQL из веб-приложения для одного вызова хранимой процедуры

Короче CASE ISNULL (qry, '') = '' THEN 'no' ELSE 'yes' END

Как преобразовать строчную дату в формате BST и GMT в формат SQL Server в качестве типа даты

SQL Server: переменная таблицы, используемая во внутреннем соединении

В чем преимущества использования VARCHAR (max), NVARCHAR (max) и VARBINARY (max) вместо TEXT, NTEXT и IMAGE?

Невозможно использовать отчет Crystal Reports 2008 от Delphi 4 для нескольких баз данных

Редактор блоков запросов от изменения представлений в SQL Server Management Studio

SQL Server: INSERT из таблицы в таблицу

Выберите наиболее подходящий атрибут в подзапросе

Sql Сервер поиска нескольких слов и ранжирования результатов

Кэширование для последующей записи в базу данных в asp.net webservice?

azure db drop pk и добавьте новый

Simple SqlCommand.ExecuteNonQuery всегда возвращает 1, даже если он не обновлял базу данных

Давайте будем гением компьютера.