SQL subquery to fetch rows by column values

Question

I have a PostgreSQL table like this:

CREATE TABLE foo(man_id, subgroup, power, grp)
AS VALUES
    (1, 'Sub_A',  4, 'Group_A'),
    (2, 'Sub_B', -1, 'Group_A'),
    (3, 'Sub_A', -1, 'Group_B'),
    (4, 'Sub_B',  6, 'Group_B'),
    (5, 'Sub_A',  5, 'Group_A'),
    (6, 'Sub_B',  1, 'Group_A'),
    (7, 'Sub_A', -1, 'Group_B'),
    (8, 'Sub_B',  2, 'Group_B'),
    (9, 'Sub_C',  2, 'Group_B');

The power calculation works like this:

Total Power of Subgroup Sub_A in the grp Group_A is (4 + 5 ) = 9
Total Power of Subgroup Sub_B in the grp Group_A is ((-1) + 1 ) = 0
Total Power of Subgroup Sub_A in the grp Group_B is ((-1) + (-1) ) = -2
Total Power of Subgroup Sub_B in the grp Group_B is (6 + 2 ) = 8

So the power of Sub_A in the Group_A is not equal to power of Sub_A in the Group_B

So the power of Sub_B in the Group_A is not equal to power of Sub_B in the Group_B

I want to query the database and fetch the rows where, for a same subgroup name total power is not equal across all the other grp names.

What would be the recommended way to do this?

I can find the sum of total power:

SELECT sum(power) AS total_power
FROM   foo
GROUP  BY grp

MySQL solution will be accepted as well.

Never used PostgreSQL but it would seem this can all be done with a left join on 2 sub querys with the Foo table. — Holmes IV, Jun 26 '18 at 23:33
Your description isn't clear to me. What do you mean by "is not equal across all the other `grp` names"? And what would be the records fetched with your example data? — sticky bit, Jun 26 '18 at 23:46

Erwin Brandstetter · Accepted Answer · 2018-06-27T00:06:42.133

One way:

SELECT f.*
FROM  (
   SELECT subgroup
   FROM  (
      SELECT subgroup, grp, sum(power) AS total_power
      FROM   foo
      GROUP  BY subgroup, grp
      ) sub
   GROUP  BY 1
   HAVING min(total_power) <> max(total_power)  -- can fail for NULL values;
   ) sg
JOIN foo f USING (subgroup);

In your example all rows qualify except for the last one with 'Sub_C'.

Closely related to your previous question:

Do all groups have equal total power for given subgroup?

Similar explanation and considerations.

db<>fiddle here

score 0 · Answer 2 · answered Jun 27 '18 at 00:27

I think a way to phrase your problem is that you want to total the power for subgroup in a group, then find if a subgroup with the same name exists in another group with a different power.

The first step is to total the powers like you want:

SELECT grp, subgroup, sum(power) as power
FROM foo
GROUP BY grp, subgroup

That should give you a result like:

grp      subgroup  power
-------  --------  -----
Group_A  Sub_A      9
Group_A  Sub_B      0
Group_B  Sub_A     -2
Group_B  Sub_B      8
Group_B  Sub_C      2

Once you have that, you can use a CTE to join the results with itself for the comparison to get what you want. You don't specify whether you want Sub_C to appear, if 'not existing' qualifies as having a 'different total power', then you would want to use a left join and check for nulls in alias b. The < in the join makes it so that each difference only appears once with the lower order group as grp1.

WITH totals AS (
    SELECT grp, subgroup, sum(power) as power
    FROM foo
    GROUP BY grp, subgroup
    ORDER BY grp, subgroup
)
SELECT a.subgroup,
       a.grp as grp1, a.power as Power1,
       b.grp as grp2, b.power as Power2
FROM totals a
    INNER JOIN totals b ON b.subgroup = a.subgroup
                           and a.grp < b.grp
WHERE b.power <> a.power
ORDER BY a.subgroup, a.grp, b.grp

score 0 · Answer 3 · answered Jun 27 '18 at 00:50

with totals as (
    select grp, subgroup, sum(power) as total_power
    from foo
    group by grp, subgroup
)
select * from totals t1
where t1.total_power <> all (
    select t2.total_power from totals t2
    where t2.subgroup = t.subgroup and t2.grp <> t1.grp
)

or

with totals as (
    select grp, subgroup, sum(power) as total_power
    from foo
    group by grp, subgroup
), matches as (
    select grp, subgroup, count(*) over (partition by subgroup, total_power) as matches
)
select * from counts where matches = 1;

score 0 · Answer 4 · answered Jun 27 '18 at 01:45

I would use window functions:

select f.*
from (select f.*,
             min(sum_value)) over (partition by group) as min_sum_value,
             max(sum_value)) over (partition by group) as max_sum_value,
      from (select f.*,
                   sum(value) over (partition by subgroup, group) as sum_value
            from foo f
           ) f
      ) f
where min_sum_value <> max_sum_value;

SQL subquery to fetch rows by column values

4 Answers4