Group php mysql overdue invoices

Question

Group php mysql overdue invoices

Navigation

#1 by (1 votes)
#2 by (-1 votes)

0

Hello, I'm a beginner, I'm learning to do select, and I would like to receive help from this wonderful community.

I need to pick up only the invoices that are with the a_vencer status of each student, it would more or less group by id the invoices that are with the status a_vencer.

I have a table called invoices with the following fields: id, child_id, value, expiration and status where each student has several invoices, each invoice has the student id, some invoices are paid and others are due, and I wanted to make a select in this table and find students who have only 1 invoice in arrears.

How would I do this? I thank the help of all you! Thank you

php mysql sql select

asked by anonymous 08.01.2018 / 18:27

2 answers

Android emulator loads but gives error Error starting application

score 1 · Answer 1

To select all columns when the student only has an invoice with the status 'a_vencer' you must use the group by function combined with having .

Next query :

SELECT id, id_aluno, valor, vencimento, status
FROM faturas
WHERE status = 'a_vencer'
GROUP BY id_aluno
HAVING count(*) = 1

You can see it working at this SQL Fiddle link.

score -1 · Answer 2

You do not have to write the name of each column of the table if it will take all of them. The Query above is not wrong, there is only redundancy unnecessary!

Optimized:

SELECT *
FROM faturas
WHERE status = 'a_vencer'
GROUP BY id_aluno
HAVING COUNT(*) = 1;