Select records for MySQL only once based on a column value

Question

Hi all, I have a table that stores transaction information. Each transaction is has a unique (auto incremented) id column, a column with the customer's id number, a column called bill_paid which indicates if the transaction has been paid for by the customer with a yes or no, and a few other columns which hold other information not relevant to my question.

I want to select all customer ids from the transaction table for which the bill has not been paid, but if the customer has had multiple transactions where the bill has not been paid I DO NOT want to select them more than once. This way I can generate that customer one bill with all the transactions they owe for instead of a separate bill for each transaction. How would I build a query that did that for me?

Answer 1

You might try the Group By operator, eg group by the customer.

Answer 2

SELECT customer, SUM(toPay) FROM .. GROUP BY customer

Answer 3

Returns exactly one customer_id for each customer with bill_paid equal to 'no':

SELECT 
    t.customer_id
FROM 
    transactions t
WHERE 
    t.bill_paid = 'no'
GROUP BY
    t.customer_id

Edit:
GROUP BY summarises your resultset.
Caveat: Every column selected must be either 'grouped by' or aggregated in some fashion. As shown by nikic you could use SUM to get the total amount owed, e.g.:

SELECT 
    t.customer_id
    , SUM(t.amount) AS TOTAL_OWED
FROM 
    transactions AS t
WHERE 
    t.bill_paid = 'no'
GROUP BY
    t.customer_id

t is simply an alias.
So instead of typing transactions everywhere you can now simply type t. The alias is not necessary here since you query only one table, but I find them invaluable for larger queries. You can optionally type AS to make it more clear that you're using an alias.