DISTINCT key word issues in SQLite-CodePudding

Trying to run a query that should bring back all mechanics and a sum of all their commissions from another table but only getting one mechanics name and a sum of all commissions. Tried writing the query in different ways but getting the same result.

The Query:

 SELECT DISTINCT m.mechID, fname || ' ' || lname AS 'Full Name', SUM(commission) AS 'Total Commissions Per Mechanic'
    FROM
         mechanics AS m
    INNER JOIN mech_commissions AS mc on m.mechID = mc.mechID
    ORDER BY "Full Name";

The output:

CodePudding user response：

I think you want an aggregation query here:

SELECT m.mechID, m.fname || ' ' || m.lname AS `Full Name`, 
       SUM(mc.commission) AS `Total Commissions Per Mechanic`
FROM mechanics AS m
INNER JOIN mech_commissions AS mc ON m.mechID = mc.mechID
GROUP BY 1, 2
ORDER BY `Full Name`;