CODEWITHSUNDEEP
sql
Ayan Bhunia
Ayan Bhunia

Reputation: 549

How to use group by with Self join

I have table like this

From this I want to show a table like

So, I tried self join

SELECT a.Name, a.Acc, a.Time, a.Amount as dep_amt, new.Amount as wid_amt, (a.Amount-new.Amount) as profit
FROM all_data as a, all_data as new
where a.Time = new.Time
and a.name = new.name
and a.acc =new.acc
and a.x= "dep”
and new.x="wit"

But , I can't use Group by(error contains nonaggregated column) with it and this sql results a table with many rows. How to do???

Upvotes: 0

Views: 74

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

This looks like conditional aggregation, not a self-join:

select Name, Acc, Time,
       sum(case when x = 'dep' then amount else 0 end) as dep_amt,
       sum(case when x = 'wit' then amount else 0 end) as wit_amt,
       sum(case when x = 'dep' then amount 
                when x = 'wit' then - amount
                else 0
           end) as profit
from t
group by Name, Acc, Time;

Here is a db<>fiddle.

The self-join doesn't work because you have multiple "dep" and "wit" rows in each group. Within each group, you get a Cartesian product of those rows, resulting in errors in the total calculation.

Upvotes: 1

sticky bit
sticky bit

Reputation: 37472

Seems like you want conditional aggregation.

SELECT name,
       acc,
       time,
       sum(CASE
             WHEN x = 'dep' THEN
               amount
             ELSE
               0
           END) dep_amt,
       sum(CASE
             WHEN x = 'wit' THEN
               amount
             ELSE
               0
           END) dep_wit,
       sum(CASE
             WHEN x = 'dep' THEN
               amount
             ELSE
               0
           END)
       -
       sum(CASE
             WHEN x = 'wit' THEN
               amount
             ELSE
               0
           END) profit
       FROM all_data
       GROUP BY name,
                acc,
                time;

Upvotes: 1

Related Questions