[Solved] group by sum and join giving duplicate rows

Question

You are getting duplicates due to join, you can get expected results without join. Try below query

select materialno, sum(billedqty), min(materialdesc), min(startdate) from billing group by materialno;

When you are joining test1 with billing table, you are getting duplicates because your materialno=”1001″ is having 2 corresponding “materialdesc” values, and “startdate” values. But the query I mentioned returns only first “materialdesc” and “startdate” of “materialno“.

Accepted Answer

You are getting duplicates due to join, you can get expected results without join. Try below query

select materialno, sum(billedqty), min(materialdesc), min(startdate) from billing group by materialno;

When you are joining test1 with billing table, you are getting duplicates because your materialno=”1001″ is having 2 corresponding “materialdesc” values, and “startdate” values. But the query I mentioned returns only first “materialdesc” and “startdate” of “materialno“.