Query to sum amount per store-CodePudding

The job given to me was to edit the query, but I could not do the operation I wanted. First of all, the query output I want to explain is as follows:

FacilityName     AmountWithoutDiscount      DiscountedAmount
Atlantis             18.305                    12.335
OneTower             4.595                     3.168
Panora               10.043                    6.831
Grand Total          32.944                    22.334

My query:

SELECT DISTINCT g.FacilityName, SUM(g.BrutPrice) AS AmountWithoutDiscount, SUM(g.NetPrice) AS DiscountedAmount
From(
SELECT e.FacilityName, b.ItemAmount, b.BrutPrice, b.NetPrice,
(SELECT DISTINCT f.ProductId FROM FAZIKI.dbo.NM_ProductCampaign f WHERE c.Id = f.ProductId AND (f.IsActive=1) AND (f.CampaignId='7') AND (f.ProductId IS NOT NULL)) as Product
FROM FAZIKI.dbo.PM_InvoiceInformation a
INNER JOIN FAZIKI.dbo.PM_InvoiceDetail b ON a.Id = b.InvoiceId 
INNER JOIN FAZIKI.dbo.PM_Product c ON b.ProductId = c.Id 
INNER JOIN FAZIKI.dbo.DF_GNFacility e ON a.FacilityId = e.Id
WHERE(a.InvoiceDate > CONVERT(DATETIME, '2021-11-08 00:00:00', 102)) AND (a.InvoiceType = 2) AND (a.IsFromNCR = 1) 
AND (c.RayonId = 1) AND (b.BrutPrice <> b.NetPrice) AND (a.IsCancel = 0)) AS g
WHERE g.Product IS NOT NULL 
GROUP BY g.FacilityName,g.BrutPrice, g.NetPrice

query result:

FacilityName  AmountWithoutDiscount    DiscountedAmount
Atlantis           8.50                   5.74
Atlantis           8.50                   5.75
Atlantis           8.50                   5.77
.
.
.
OneTower          8.50                    5.70
OneTower          8.50                    5.83
OneTower          8.50                    5.87
.
.
.
Panora           8.50                     5.66
Panora           8.50                     5.78
Panora           8.50                     5.81

How can I do the sum of my query result as in the field I mentioned above and the sum of each store? I brought my query up to a certain point, but I could not do more. Can you help me?

CodePudding user response：

I think you should delete from your query:

Distinct - there is no point in having "group by" and "distinct".
g.BrutPrice and g.NetPrice from the group by. If you have them in group by there is no point to use aggregation function such as "sum" on them.

so:

SELECT g.FacilityName, SUM(g.BrutPrice) AS AmountWithoutDiscount, SUM(g.NetPrice) AS DiscountedAmount
From(
SELECT e.FacilityName, b.ItemAmount, b.BrutPrice, b.NetPrice,
(SELECT DISTINCT f.ProductId FROM FAZIKI.dbo.NM_ProductCampaign f WHERE c.Id = f.ProductId AND (f.IsActive=1) AND (f.CampaignId='7') AND (f.ProductId IS NOT NULL)) as Product
FROM FAZIKI.dbo.PM_InvoiceInformation a
INNER JOIN FAZIKI.dbo.PM_InvoiceDetail b ON a.Id = b.InvoiceId 
INNER JOIN FAZIKI.dbo.PM_Product c ON b.ProductId = c.Id 
INNER JOIN FAZIKI.dbo.DF_GNFacility e ON a.FacilityId = e.Id
WHERE(a.InvoiceDate > CONVERT(DATETIME, '2021-11-08 00:00:00', 102)) AND (a.InvoiceType = 2) AND (a.IsFromNCR = 1) 
AND (c.RayonId = 1) AND (b.BrutPrice <> b.NetPrice) AND (a.IsCancel = 0)) AS g
WHERE g.Product IS NOT NULL 
GROUP BY g.FacilityName