How do I get the aggregated sum of 2 different columns as one in SQL?

Question

I have a table like this -

Product | Competitor | Comparison Count
 A           B            3
 C           D            5
 B           A            2
 E           A            1

And I need a Output for product A, its competitor and the count like this -

Product | Competitor | Comparison Count
 A           B           5
 A           E           1

I am using postgresql and am kind of clueless on how to aggregate the Comparison count for product A when it's present on both Product and Competitor columns as above. Any leads would be appreciated. Thanks!

klin · Accepted Answer · 2019-11-14 16:55:49Z

2

Use conditional columns in select. In the example we are searching for B, the data a bit extended:

with my_table(product, competitor, comparison_count) as (
values
    ('A', 'B', 3),
    ('C', 'D', 5),
    ('B', 'A', 2),
    ('B', 'D', 2),
    ('E', 'A', 1),
    ('D', 'B', 4)
)

select
    case when product = 'B' then product else competitor end as product,
    case when product = 'B' then competitor else product end as competitor,
    sum(comparison_count) as comparison_count
from my_table
where product = 'B' or competitor = 'B'
group by 1, 2
order by 2

 product | competitor | comparison_count 
---------+------------+------------------
 B       | A          |                5
 B       | D          |                6
(2 rows)

edited Nov 14, 2019 at 16:55

answered Nov 14, 2019 at 5:49

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Abhigyan Sarma Over a year ago

I think this would work for the example I gave but wouldn't work if I want to find the counts for another product, say B?

Gordon Linoff · Accepted Answer · 2019-11-14 13:15:35Z

0

If you want to preserve the original values in your columns, this is a bit trickier. If you just use least() and greatest(), then you get A/E rather than E/A.

This can be handled with some additional logic:

select (case when count(*) filter (where product < competitor) > 0 
             then least(product, competitor) else greatest(product, competitor)
        end) as product,
       (case when count(*) filter (where product < competitor) > 0 
             then greatest(product, competitor) else least(product, competitor)
        end) as competitor,
       sum(comparison_count)
from t
group by least(product, competitor), greatest(product, competitor);

Here is a db<>fiddle.

answered Nov 14, 2019 at 13:15

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

How do I get the aggregated sum of 2 different columns as one in SQL?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related