Grouping by line items in mysql -
i need result of 1 column different on each row. this how data appears: ticketnumber legnumber commission 100 1 50 100 2 50 this how need appear: ticketnumber legnumber commission 100 1 100 100 2 0 so when legnumber = 1 need commission group ticketnumber, when legnumber > 1 commission should zero. tried writing case statement no avail. suggestions on how accomplish this? you can use group by attribute select table1.ticketnumber, legnumber , case when legnumber = 1 sum(table2.commission) else 0 end commission table1 join (select ticketnumber, sum(commission) commission table1 group ticketnumber) table2 on table1.ticketnumber = table2.ticketnumber group table1.ticketnumber, legnumber order table1.ticketnumber, legnumber sql fiddle