CODEWITHSUNDEEP
phpmysqlgroup-bysum
David38
David38

Reputation: 21

Return all the rows in a group

Is there a way of getting all the rows used in a group?

As you would expect, the query below only returns one row per custref. But if totalamount is > 0, I want it to return all the rows in the group. Otherwise none.

$sql = "SELECT
    debtors.custref,
    debtors.amount,
    users.bnkSort,
    users.bnkAccNum,
    users.bnkAccName,
    SUM(debtors.amount) AS totalamount
    FROM debtors
    LEFT JOIN users ON debtors.custref=users.custref
    WHERE
    users.bank = 'Yes'
    GROUP BY debtors.custref
";

Upvotes: 2

Views: 80

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Your query has multiple columns that are invalidated by the group by -- columns that are not the same in rows in the group. Also, the where clause is undoing the left join. So, this is basically your query:

SELECT d.custref, SUM(d.amount) AS totalamount
FROM debtors d JOIN 
     users u
     ON d.custref = u.custref
WHERE u.bank = 'Yes'
GROUP BY d.custref;

To get all the fields, you can use this as a subquery:

SELECT d.*, u.*
FROM (SELECT d.custref, SUM(d.amount) AS totalamount
      FROM debtors d JOIN 
           users u
           ON d.custref = u.custref
      WHERE u.bank = 'Yes'
      GROUP BY d.custref
      HAVING totalamount > 0
     ) dc JOIN
     debtors d
     ON dc.custref = d.custref JOIN
     users u
WHERE u.bank = 'Yes';

 ON dc.custref = u.custref

Upvotes: 1

Related Questions