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