SUM GROUP Multiple Sub Querys-CodePudding

I have a Query that gives me the correct information, However the Data is not displaying Grouped and Summed, Possibly due to the joins and construction. However if i change the order, then i end up with duplicated values because of the last Join Table having multiple lines. benefitdistribution, There is multiple values per PLU

This query provides me with the correct results, How would i go about Summing the output of this query without creating a view and then summing the view?

 SELECT dbo.plumovement.deptno,
       dbo.departmentdata.NAME          AS DeptName,
       dbo.groupdata.no                 AS GroupNo,
       dbo.groupdata.NAME               AS GroupName,
       Sum(dbo.plumovement.qty)         AS SalesQty,
       Sum(dbo.plumovement.value)       AS SalesValue,
       Sum(dbo.plumovement.netvalue)    AS NetValue,
       Sum(dbo.plumovement.cost)        AS Cost,
       Sum(dbo.plumovement.vatvalue)    AS VatValue,
       Max(dbo.departmentdata.targetgp) AS TargetGP,
     -- isNull(DistributionTotals.discountno, - 1) AS DiscountNo,
       isNull(DistributionTotals.DiscountQty, 0) AS BenefitQty,
       isNull(DistributionTotals.DiscountValue, 0) AS DiscountValue,
       CASE
         WHEN Sum(plumovement.cost) = 0 THEN 100
         ELSE
(
( ( Sum(plumovement.netvalue) )   ( Sum(plumovement.cost) ) ) / ( Sum(plumovement.netvalue) ) * 100 )
END                              AS [GP]
FROM   dbo.plumovement
       LEFT OUTER JOIN dbo.departmentdata
                    ON dbo.plumovement.deptno = dbo.departmentdata.no
       LEFT OUTER JOIN dbo.groupdata
                    ON dbo.departmentdata.parentgroup = dbo.groupdata.no
       LEFT OUTER JOIN (SELECT txnid,
                               pluno,
                            --   discountno,
                               Sum(qty)   AS DiscountQty,
                               Sum(value) AS DiscountValue
                        FROM   dbo.benefitdistribution
                        GROUP  BY txnid,
                                  pluno,
                                  discountno) AS DistributionTotals
                    ON DistributionTotals.txnid = plumovement.txnid
                       AND DistributionTotals.pluno = plumovement.pluno
WHERE  ( dbo.plumovement.movetype = 0 )
       AND ( dbo.plumovement.termno > 0 )
       AND ( dbo.plumovement.consolidatedtermno = 0 )
       AND ( dbo.plumovement.processed = 1 )
       AND ( plumovement.date BETWEEN '2022-10-01' AND '2022-11-01' )

GROUP  BY dbo.groupdata.no,
          dbo.groupdata.NAME,
          dbo.plumovement.deptno,
          dbo.departmentdata.NAME,
          DistributionTotals.DiscountQty,
          DistributionTotals.DiscountValue

Out putted results:

deptno	DeptName	GroupNo	GroupName	SalesQty	SalesValue	NetValue	Cost	VatValue	TargetGP	BenefitQty	DiscountValue	GP
1	Draught Beer Cider	1	DRINK	-16	164.9	137.4167	-15.191	27.4833	40	0	0	88.94
1	Draught Beer Cider	1	DRINK	-1	4.3373	3.6144	-0.8682	0.7229	40	1	0.1627	75.97
1	Draught Beer Cider	1	DRINK	-6	25.65	21.375	-5.2092	4.275	40	2	0.45	75.62
1	Draught Beer Cider	1	DRINK	-1	3.2442	2.7035	-0.8682	0.5407	40	2	1.2558	67.88
1	Draught Beer Cider	1	DRINK	-4	7	5.8334	-5.1318	1.1666	40	2	3.5	12.02
1	Draught Beer Cider	1	DRINK	-4	17.1	14.25	-3.4728	2.85	40	4	0.9	75.62
1	Draught Beer Cider	1	DRINK	-4	7	5.8333	-5.1318	1.1667	40	4	7	12.02
9	Red Wine	1	DRINK	-1	-16.45	-13.7083	-1.4532	-2.7417	20	1	22	110.6
10	Rose Wine	1	DRINK	-1	3.55	2.9583	-0.7351	0.5917	45	0	0	75.15
10	Rose Wine	1	DRINK	-1	4.5058	3.7548	-1.4699	0.751	45	2	1.7442	60.85

To confirm there should be 1 line for each unique DeptNo for example

deptno	DeptName	GroupNo	GroupName	SalesQty	SalesValue	NetValue	Cost	VatValue	TargetGP	BenefitQty	DiscountValue	GP
1	Draught Beer Cider	1	DRINK	-36	229.2315	191.0263	-35.873	38.2052	80	15	13.2685	79.00

The TargetGP does not need summing as this is just one value of the the "departmentdata.targetgp" as shown in the example. The GP However needs to be calculated from the outputted summed results. As it should be around 75-80 of the top of my head

CodePudding user response：

Use a CTE:

with cte as (
SELECT dbo.plumovement.deptno,
       dbo.departmentdata.NAME          AS DeptName,
       dbo.groupdata.no                 AS GroupNo,
       dbo.groupdata.NAME               AS GroupName,
       Sum(dbo.plumovement.qty)         AS SalesQty,
       Sum(dbo.plumovement.value)       AS SalesValue,
       Sum(dbo.plumovement.netvalue)    AS NetValue,
       Sum(dbo.plumovement.cost)        AS Cost,
       Sum(dbo.plumovement.vatvalue)    AS VatValue,
       Max(dbo.departmentdata.targetgp) AS TargetGP,
     -- isNull(DistributionTotals.discountno, - 1) AS DiscountNo,
       isNull(DistributionTotals.DiscountQty, 0) AS BenefitQty,
       isNull(DistributionTotals.DiscountValue, 0) AS DiscountValue,
       CASE
         WHEN Sum(plumovement.cost) = 0 THEN 100
         ELSE
(
( ( Sum(plumovement.netvalue) )   ( Sum(plumovement.cost) ) ) / ( Sum(plumovement.netvalue) ) * 100 )
END                              AS [GP]
FROM   dbo.plumovement
       LEFT OUTER JOIN dbo.departmentdata
                    ON dbo.plumovement.deptno = dbo.departmentdata.no
       LEFT OUTER JOIN dbo.groupdata
                    ON dbo.departmentdata.parentgroup = dbo.groupdata.no
       LEFT OUTER JOIN (SELECT txnid,
                               pluno,
                            --   discountno,
                               Sum(qty)   AS DiscountQty,
                               Sum(value) AS DiscountValue
                        FROM   dbo.benefitdistribution
                        GROUP  BY txnid,
                                  pluno,
                                  discountno) AS DistributionTotals
                    ON DistributionTotals.txnid = plumovement.txnid
                       AND DistributionTotals.pluno = plumovement.pluno
WHERE  ( dbo.plumovement.movetype = 0 )
       AND ( dbo.plumovement.termno > 0 )
       AND ( dbo.plumovement.consolidatedtermno = 0 )
       AND ( dbo.plumovement.processed = 1 )
       AND ( plumovement.date BETWEEN '2022-10-01' AND '2022-11-01' )

GROUP  BY dbo.groupdata.no,
          dbo.groupdata.NAME,
          dbo.plumovement.deptno,
          dbo.departmentdata.NAME,
          DistributionTotals.DiscountQty,
          DistributionTotals.DiscountValue
)
select
deptno,
DeptName,
GroupNo,
GroupName,
sum(SalesQty) as SalesQty,
sum(SalesValue) as SalesValue,
sum(NetValue) as NetValue,
sum(Cost) as Cost,
sum(VatValue) as VatValue,
sum(TargetGP) as TargetGP,
sum(DiscountNo) as DiscountNo,
sum(BenefitQty) as BenefitQty,
sum(DiscountValue) as DiscountValue,
sum(GP) as GP
from cte
group by deptno, DeptName, GroupNo, GroupName