CODEWITHSUNDEEP
sqlpostgresqlunionaggregate-functions
Ashok Barthwal
Ashok Barthwal

Reputation: 11

SQL query grouping by two different columns

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

Upvotes: 1

Views: 79

Answers (2)

KumarHarsh
KumarHarsh

Reputation: 5094

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

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656281

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:

Upvotes: 2

Related Questions