I have table like below

I need to get the difference of sum while doing group by using pid and qid

Like

SELECT
    /* ??? */
FROM
    tbl
GROUP BY
    pid,
    qid

CodePudding user response：

Use conditional aggregation:

SELECT 11 AS id1,
       12 AS id2,
       qid,
       ABS(SUM(CASE pid WHEN 11 THEN value WHEN 12 THEN -value END)) AS diff
FROM   tbl
GROUP BY qid;

Which, for the sample data:

CREATE TABLE tbl (Pid, qid, value) AS
  SELECT 11, 13, 160 FROM DUAL CONNECT BY LEVEL <= 5 UNION ALL
  SELECT 12, 13,  43 FROM DUAL CONNECT BY LEVEL <= 6 UNION ALL
  SELECT 12, 13,  42 FROM DUAL CONNECT BY LEVEL <= 1 UNION ALL
  SELECT 11, 14,  30 FROM DUAL CONNECT BY LEVEL <= 2 UNION ALL
  SELECT 11, 14,  40 FROM DUAL CONNECT BY LEVEL <= 1 UNION ALL
  SELECT 12, 14,  40 FROM DUAL CONNECT BY LEVEL <= 5;

Outputs:

fiddle

CodePudding user response：

Looks like you want a difference of sum between all pairs of different pid per each qid. The easiest and most reliable solution in this case would be to use a simple self join, like this:

with agg as (
select pid, qid
  , count(*) as cnt
  , sum(?) as sum1
from your_table
group by pid, qid
)
select
 t1.pid as id1
,t2.pid as id2
,abs(t2.sum1-t1.sum1) as diff
from agg t1
     join agg t2
       on t1.qid=t2.qid
       and t1.pid<t2.pid

Obviously, we could optimize it if we were sure that you have just 2 different PIDs per each qid, but you haven't mention it