CODEWITHSUNDEEP
sqlsumleft-join
tom
tom

Reputation: 225

Sql join does not return some rows with groupBy

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

Upvotes: 0

Views: 81

Answers (3)

GMB
GMB

Reputation: 222592

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

Upvotes: 1

Greg
Greg

Reputation: 94

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'.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270583

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.

Upvotes: 0

Related Questions