Sql query to add consecutive row values until a condition is met

Question

I have a small SQL table as follows:

    create table temp (
        company varchar2(10) not null,
        sales NUMBER,
        parent_b VARCHAR2(10),
        PRIMARY KEY (company),
        CONSTRAINT company_branch_reference FOREIGN KEY (parent_b)
        REFERENCES temp(company)
    );

The contents of the table are as follows:

+---------+-------+----------+
| COMPANY | SALES | PARENT_B |
+---------+-------+----------+
| A1      |    20 | A1       |
| A2      |    30 | A1       |
| A3      |    30 | A1       |
| A4      |    40 | A2       |
| A5      |    10 | A2       |
| A6      |    20 | A3       |
| B1      |    40 | B1       |
| B2      |    20 | B1       |
+---------+-------+----------+

In this case, each company has a reference to the parent company. For example, A1 itself is a parent so the Parent column has A1 for A2, A3 the Parent column has A1

I want to calculate the total sales of each parent as follows:
The parent A1 has a total sale of 20+30+30+40+10+20 = 150
The parent B1 has a total sale of 40+20 = 60

The output should be as follows:

+----------+-------+
| Parent_b | Sales |
+----------+-------+
| A1       |   150 |
| B1       |    60 |
+----------+-------+

Can someone please help me out with a MySQL query for this scenario.

Thanks in advance!

first, is it mysql or oracle? second if A1 company does not have real parent, then dont use A1 as parent. this complicates everything. — Derviş Kayımbaşıoğlu, Sep 08 '20 at 05:35
https://stackoverflow.com/questions/22276790/mysql-cumulative-sum-grouped-by-date — ashan, Sep 08 '20 at 05:35
@PradnyaAlchetti then you can use the query I have given, it should work. — Atif, Sep 08 '20 at 08:49

score 1 · Answer 1 · answered Sep 08 '20 at 05:53

If you are using Oracle you can use Hierarchial query to solve this:

WITH CTE1 AS (SELECT a.*, LEVEL, CONNECT_BY_ROOT COMPANY AS PARENT
FROM table1 a
START WITH  parent_b IN ('A1', 'B1')
CONNECT BY NOCYCLE PRIOR  company = parent_b)
SELECT SUM(SALES), PARENT FROM CTE1 
WHERE PARENT IN ('A1', 'B1') 
GROUP BY PARENT;

Sql query to add consecutive row values until a condition is met

1 Answers1