CODEWITHSUNDEEP
sqlpostgresql
Abhigyan Sarma
Abhigyan Sarma

Reputation: 35

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

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!

Upvotes: 1

Views: 59

Answers (2)

klin
klin

Reputation: 121494

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)

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269463

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.

Upvotes: 0

Related Questions