Cumulative Sum by Groups

Question

Cumulative Sum by Groups

MICHALDV

Old Hand

Points: 328
More actions
May 22, 2023 at 9:34 am

#4194669
HEY,
I HAVE TWO TABLES: CUSTOMERS AND USERS PER CUSTOMER
I NEED TO CREATE TWO CUSTOMERS GROUPS: DVIR & RON - 'CRNTER GROUP', AND DANNY & DAVID 'OTHER GROUP'.
AFTER THAT I NEED TO SUM THE NUMBER OF USERS FOR EACH GROUP.
I STARED BY USING UNION ALL AND PARTITION BY:
```
WITH "CUST-CTE" AS
(
SELECT ROW_NUMBER() OVER (ORDER BY NAME) RN , NAME
FROM CUSTOMERS
)
SELECT 'OTHER', NAME
FROM "CUST-CTE"
WHERE RN = 1
UNION ALL
SELECT 'OTHER', NAME
FROM "CUST-CTE"
WHERE RN = 2
UNION ALL
SELECT 'CENTER', NAME
FROM "CUST-CTE"
WHERE RN = 3
UNION ALL
SELECT 'CENTER', NAME
FROM "CUST-CTE"
WHERE RN = 4
```
AND NOW I AM STUCK WITH THE SUM.
ANY HELP PLEASE?
- This topic was modified 1 year, 6 months ago by MICHALDV.

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

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

Phil Parkin SSC Guru Points: 246970 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;

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

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 1 year, 6 months ago by MICHALDV.

Phil Parkin SSC Guru Points: 246970 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;

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel