Cumulative Sum by Groups

Question

Cumulative Sum by Groups

Viewing 3 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247183 More actions · Answer 1

Something like this?

DROP TABLE IF EXISTS #Customer;

CREATE TABLE #Customer
(
    Name VARCHAR(50) NOT NULL
   ,CustomerNumber INT NOT NULL
);

INSERT #Customer
(
    Name
   ,CustomerNumber
)
VALUES
('Danny', 1)
,('David', 2)
,('DVIR', 3)
,('Ron', 4);

DROP TABLE IF EXISTS #UserCustomer;

CREATE TABLE #UserCustomer
(
    Code CHAR(1) NOT NULL
   ,CustomerNumber INT NOT NULL
);

INSERT #UserCustomer
(
    Code
   ,CustomerNumber
)
VALUES
('A', 1)
,('B', 2)
,('C', 1)
,('D', 1)
,('E', 3);

DROP TABLE IF EXISTS #CustomerGroup;

CREATE TABLE #CustomerGroup
(
    CustomerNumber INT NOT NULL
   ,CustomerGroup VARCHAR(50) NOT NULL
);

INSERT #CustomerGroup
(
    CustomerNumber
   ,CustomerGroup
)
VALUES
(1, 'Other Group')
,(2, 'Other Group')
,(3, 'CRNTER Group')
,(4, 'CRNTER Group');

SELECT cg.CustomerGroup
      ,UserCount = COUNT (uc.Code)
FROM #Customer c
    JOIN #CustomerGroup cg
        ON cg.CustomerNumber = c.CustomerNumber
    JOIN #UserCustomer uc
        ON uc.CustomerNumber = c.CustomerNumber
GROUP BY cg.CustomerGroup;

MICHALDV Old Hand Points: 328 More actions · Answer 2

Hey Phil, thanks for your answer!

Actually I am trying to solve the quastion without creating a 'real' table, but by creating a virtual table - something like that:

WITH SimpleGroupBy 
AS 
( 
SELECT productID, productName, SUM(unitPrice) PriceSum 
FROM products 
GROUP BY productID, productName 

) 
SELECT productID , productName, (SELECT SUM(PriceSum) FROM SimpleGroupBy c2 WHERE c2.productID >= c1.productID) 
FROM SimpleGroupBy c1;

This reply was modified 2 years, 5 months ago by MICHALDV.

Phil Parkin SSC Guru Points: 247183 More actions · Answer 3

OK, simply change my final SELECT:

WITH CustomerGroup
AS (SELECT *
    FROM
    (
        VALUES
            (1, 'Other Group')
           ,(2, 'Other Group')
           ,(3, 'CRNTER Group')
           ,(4, 'CRNTER Group')
    ) x(CustomerNumber, CustomerGroup) )
SELECT cg.CustomerGroup
      ,UserCount = COUNT (uc.Code)
FROM #Customer c
    JOIN CustomerGroup cg
        ON cg.CustomerNumber = c.CustomerNumber
    JOIN #UserCustomer uc
        ON uc.CustomerNumber = c.CustomerNumber
GROUP BY cg.CustomerGroup;