How to Get Summary (Sum) from Name Wise and Product Wise in SQL Server

sql serversql server 2014

I have a 3 tables,

1.Payment Collector

enter image description here

  1. Product

enter image description here

  1. Payment

enter image description here

Payment collectors are collecting payments product wise and stored into payment table. products are assigned to payment collectors some products are not assign to collectors.. question is how to retrieve all the payment collectors product wise total collected amount result should be,

enter image description here

Note: data should be retrieve horizontal way and vertical way (two queries)

Thanks.

Best Answer

This is a long one, but it should work for you, accomplishing what you want.

The Setup

USE [test];
GO;

CREATE TABLE dbo.PaymentCollector
    (
    PaymentCollectorID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , CollectorName VARCHAR(100) NULL
    );

CREATE TABLE dbo.Product
    (
    ProductID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , ProductName VARCHAR(100) NULL
    );

CREATE TABLE dbo.Payment
    (
    PaymentID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , PaymentCollectorID INT NOT NULL REFERENCES dbo.PaymentCollector(PaymentCollectorID)
    , ProductID INT NOT NULL REFERENCES dbo.Product (ProductID)
    , Amount DECIMAL(7,2) NOT NULL
    );

INSERT INTO dbo.PaymentCollector
(CollectorName)
VALUES ('John')
    , ('Anna')
    , ('Lee')
    , ('Andrew');

INSERT INTO dbo.Product
(ProductName)
VALUES ('Card')
    , ('Loan')
    , ('OD');

INSERT INTO dbo.Payment
(PaymentCollectorID, ProductID, Amount)
VALUES (1,1,100.00)
    , (2,3,50.00)
    , (4,1,200.00)
    , (1,2,30.00)
    , (3,2,40.00)
    , (1,3,10.00)
    , (2,2,100.00)
    , (1,3,250.00)
    , (3,2,40.00)
    , (4,3,60.00);

Query 1 - Horizontal

WITH CTE_Data AS
    (
    SELECT P.PaymentCollectorID
        , P.ProductID
        , SUM(Amount) AS Total
    FROM dbo.Payment AS P
    GROUP BY P.PaymentCollectorID
        , P.ProductID
    )
    , CTE_AllList AS
    (
    SELECT PC.PaymentCollectorID
        , PR.ProductID
    FROM dbo.PaymentCollector AS PC
        CROSS JOIN dbo.Product AS PR
    )
SELECT PC.CollectorName
    , PR.ProductName
    , C.Total
FROM CTE_AllList AS A
    LEFT OUTER JOIN CTE_Data AS C ON C.PaymentCollectorID = A.PaymentCollectorID AND C.ProductID = A.ProductID
    INNER JOIN dbo.Product AS PR ON PR.ProductID = A.ProductID
    INNER JOIN dbo.PaymentCollector AS PC ON PC.PaymentCollectorID = A.PaymentCollectorID;

Query 2 - Vertical (using Pivot)

WITH CTE_Data AS
    (
    SELECT P.PaymentCollectorID
        , P.ProductID
        , SUM(Amount) AS Total
    FROM dbo.Payment AS P
    GROUP BY P.PaymentCollectorID
        , P.ProductID
    )
    , CTE_AllList AS
    (
    SELECT PC.PaymentCollectorID
        , PR.ProductID
    FROM dbo.PaymentCollector AS PC
        CROSS JOIN dbo.Product AS PR
    )
    , CTE_Pivot AS
    (
    SELECT PC.CollectorName
        , PR.ProductName
        , C.Total
    FROM CTE_AllList AS A
        LEFT OUTER JOIN CTE_Data AS C ON C.PaymentCollectorID = A.PaymentCollectorID AND C.ProductID = A.ProductID
        INNER JOIN dbo.Product AS PR ON PR.ProductID = A.ProductID
        INNER JOIN dbo.PaymentCollector AS PC ON PC.PaymentCollectorID = A.PaymentCollectorID
    )
    SELECT CollectorName, [Card], [Loan], [OD]
    FROM CTE_Pivot
        PIVOT (SUM(Total) FOR ProductName IN ([Card], [Loan], [OD])) AS PT;
Related Question