I have a simple table:
id | from | tu | status_os1 | status_os2
status_os1
and status_os2
can be only paid
or unpaid
.
I want to show a message "You have all dues paid"
.
So my question is – what should I write in the SQL
part to give an answer only if all records have values "paid"
?
I was trying do it in below way but it also failed:
$rezultat = $polacz->query("SELECT case when COUNT(status_os1) = 0 then 1 end FROM skladki WHERE status_os1='unpaid'");
$wynik = $rezultat->num_rows;
if($wynik>0)
{
echo "You have all dues paid";
}
I would approach this from the opposite side: you give the message if none of the rows has either status_o1 or status_o2 as ‘unpaid’:
$rezultat = $polacz->query("SELECT COUNT(*) AS cnt FROM skladki WHERE status_os1='unpaid' or status_os2='unpaid'");
$row = $rezultat ->fetch_assoc();
$wynik = $row["cnt"];
if($wynik==0)
{
echo "You have all dues paid";
}
Answer:
SELECT COUNT(*) FROM skladki WHERE status_os1 = “paid” and status_os2 = “paid”
Answer:
You can probably use CASE
expression in COUNT()
like
SELECT
COUNT(case when status_os1 = 'paid' and status_os2 = 'paid' then 1 else 0 end)
FROM skladki;
Answer:
You need a subquery that filters out all records where, for that person or client, there is no record in the table with a value of ‘Unpaid’.
Select * from table t
where not exists
(Select * from table
Where id = t.Id
and 'unpaid' in
(status_os1, status_os2))
Answer:
Try it from reverse side:
SELECT COUNT(*) FROM skladki WHERE status_os1 = "unpaid" or status_os2 = "unpaid"
If there are at least 1 record – some record has unpaid status 🙂
Answer:
Use a case
expression to conditionally count all unpaid items. If 0 returned, return all dues paid message.
select case when count(case when status_os1 = 'unpaid' or status_os2 = 'unpaid' then 1 end) = 0 then 'You have all dues paid'
else 'You have unpaid dues'
end
from skladki