SQL query grouping by two different columns

Question

I want a query in which data is grouped by reseller_id where parent_res_id = 0. All other rows shall use the parent_res_id instead of reseller_id.

For example, parent_res_id 0 (admin account) spawned two reseller_id: 1 and 2. Each of them created another reseller (3 and 4 respectively). I want to group rows of the children with their parents, so rows of reseller_id 3 are added to 1 and rows of reseller_id 4 are added to 2. Basically it's a tree of depth 1.

Table:

duration  call_charge reseller_id  parent_res_id
2             1              1           0
3             2              2           0
4             3              3           1
5             4              4           2
6             5              5           1
7             6              6           5

Desired result:

sum(duration)  sum(call_charege) reseller_id 
19                 15                 1
8                  6                  2

@CoderofCode: I think I did. *Only* had to read it like 5 times ... — Erwin Brandstetter, Feb 11 '15 at 11:05
@ErwinBrandstetter - Are you sure that OP is using Postgresql — Pரதீப், Feb 11 '15 at 11:18

score 2 · Answer 1 · edited May 23 '17 at 11:43

2

With UNION ALL:

SELECT reseller_id, sum(duration) AS sum_dur, sum(call_charge) AS sum_char
FROM  (
   SELECT reseller_id, duration, call_charge
   FROM   tbl
   WHERE  parent_res_id = 0

   UNION ALL
   SELECT parent_res_id, duration, call_charge
   FROM   tbl
   WHERE  parent_res_id <> 0
   ) sub
GROUP  BY 1;

Alternative with a CASE expression:

SELECT CASE WHEN parent_res_id = 0
            THEN reseller_id
            ELSE parent_res_id
       END AS reseller_id
     , sum(duration) AS sum_dur, sum(call_charge) AS sum_char
FROM   tbl
GROUP  BY 1;

Same result. Probably faster, since it only needs a single table scan.

For trees with a depth greater than 1 or 2, a recursive CTE would be the right technique. Example:

Tree Structure and Recursion

edited May 23 '17 at 11:43

Community

1
1

answered Feb 11 '15 at 11:02

Erwin Brandstetter

605,456
145
1,078
1,228

sorry for not able to explain to myself but i want that if admin created two reseller let us say reseller1 and reseller2 then parent_res_id for both of them will be '0' and reseller_id will be '1' and '2' respectively. now reseller 1 created is own reseller let us say reseller3 then parent_res_id for him will be '1' and reseller id will be '3' now i want that result will have two reseller which is created by admin that is reseller1 and reseller2 but the data of reseller3 will be added in reseller1 because reseller1 created it . – Ashok Barthwal Feb 11 '15 at 11:17
@AshokBarthwal: I think I understood that and my query implements what you are asking. Result as desired. I added an alternative query and took the liberty to also rewrite your explanation in the question. – Erwin Brandstetter Feb 11 '15 at 11:43

score 0 · Answer 2 · answered Feb 11 '15 at 11:53

Will it work with other sample data ?Tell me.

;WITH CTE
AS (
    SELECT 2 duration
        ,1 call_charge
        ,1 reseller_id
        ,0 parent_res_id

    UNION ALL

    SELECT 3
        ,2
        ,2
        ,0

    UNION ALL

    SELECT 4
        ,3
        ,3
        ,1

    UNION ALL

    SELECT 5
        ,4
        ,4
        ,2
    )
    ,CTE1
AS (
    SELECT reseller_id
        ,duration
        ,call_charge
        ,parent_res_id
    FROM cte
    WHERE parent_res_id = 0

    UNION ALL

    SELECT b.reseller_id
        ,a.duration + b.duration
        ,a.call_charge + b.call_charge
        ,a.parent_res_id
    FROM cte a
    INNER JOIN cte1 b ON a.parent_res_id = b.reseller_id
    WHERE b.parent_res_id = 0
    )
SELECT reseller_id
    ,duration [sum(duration)]
    ,call_charge [sum(call_charege)]
FROM cte1
WHERE parent_res_id > 0
ORDER BY reseller_id

SQL query grouping by two different columns

2 Answers2