Sql join does not return some rows with groupBy

Question

I am trying to learn sql.I do some practices.I created a table which called Student.

Id | Name   | Amount
1  | Jone   | 100
2  | Jack   | 200
3  | Emily  | 300
4  |Haaland | 500
7  |Ted     | 700

I also created Orders table like that:

 Id | Name    | Amount  | Dıscount
 1  | Jone    | 100     | 10
 2  | Jack    | 112     | 20
 3  | Emily   | 300     | 30
 4  |Haaland  | 500     | 50
 5  |Jack     | 88      | 12
 7  |Ted      | 150     | 235

My query is:

select a1.Id Id ,a1.Name Name, a1.Amount Amount , sum(a2.discount) 
from student a1 
  left outer join orders a2 
          on a1.Id=a2.Id 
         and a1.Name=a2.Name 
        and a1.Amount = a2.Amount 
group by a1.Id, a1.Name, a1.Amount

Result:

Id | Name   | Amount  | Dıscount
 1  | Jone   | 100     | 10    
 3  | Emily  | 300     | 30
 4  |Haaland | 500     | 50
 2  | Jack   | 200     | null
 7  | Ted    | 700     | null

I get null value for the jack row.I have to use a1.Amount=a2.Amount because I remove amount constraint Ted'discount also appears.

Expected Result :

 Id | Name   | Amount  | Dıscount
 1  | Jone   | 100     | 10    
 3  | Emily  | 300     | 30
 4  |Haaland | 500     | 50
 2  | Jack   | 200     | 32
 7  | Ted    |700      | null

I am not sure what should be problem statement. But looking at your result, it seems Jack row has some space or something like this in the name and thats why you cant see Jack row. What do you expect for Jones? — Koushik Roy
– Koushik Roy, Commented Sep 18, 2020 at 15:48
I want to add jack row but there are two jack rows on orders table.I think it causes problem. — tom
– tom, Commented Sep 18, 2020 at 15:51
nope, you are joining on a1.Id=a2.Id, this will result 2 | Jack | 200 and remove the other row in order table. — Koushik Roy
– Koushik Roy, Commented Sep 18, 2020 at 15:53
I am sorry.I wrote my question with wrong data.I updated my question. — tom
– tom, Commented Sep 18, 2020 at 16:18
What is the logic for wanting the Jack discounts and not wanting the Ted discount? Is it because orders.amount sum to student.amount for Jack but not for Ted? — EdmCoff
– EdmCoff, Commented Sep 18, 2020 at 17:07

GMB · Accepted Answer · 2020-09-18 17:30:17Z

1

I think the logic you want is to pre-aggregate the orders of each name in a subquery, then join by name and amount:

select s.id , s.name, s.amount, o.discount
from student s
left join (
    select name, sum(amount) amount, sum(discount) discount 
    from orders 
    group by name
) o on o.name = s.name and o.amount = s.amount

answered Sep 18, 2020 at 17:30

GMB

224k25 gold badges103 silver badges151 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2020-09-18 16:44:20Z

0

What is the confusion? In one row you have:

id    name    amount
2     Jack    200

And in the other:

id    name    amount
2     Jack    112

Your join requires equality on all three columns. The amounts don't match, so there is no match for Jack's row and the amount is null.

Your question is not clear on what you actually want to do, so I'll stop here.

answered Sep 18, 2020 at 16:44

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Comments

Greg · Accepted Answer · 2020-09-18 17:07:54Z

0

The amount for Jack does not match (200 in Student, 88 and 112 in Orders), so nothing can be joined ON a1.Amount = a2.Amount for that record. However, Please be advised that even if one of the values in Amount does match, the GROUP BY function will still not know which Amount you want associated with 'Jack'.

answered Sep 18, 2020 at 17:07

Greg

1041 gold badge1 silver badge7 bronze badges

Collectives™ on Stack Overflow

Sql join does not return some rows with groupBy

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related