Group records only if condition is true

[hillvalley45]

Hi guys, i really need your help to try and solve this problem
I need to group the following records, to sum the amount but only when for the same Client in the same date with the same Purchase date, the OrderPaid is Yes in all records

Reference	Date	Cur	Cliente	PurchaseDate	Amount	OrderPaid
12345678	06-06-20	EUR	AAAAA	30-01-20	100	Yes
89123455	06-06-20	EUR	AAAAA	30-01-20	10	Yes
14635677	28-04-20	EUR	BBBBB	20-04-20	5	Yes
75688946	28-04-20	EUR	BBBBB	20-04-20	90	No

The result i´m trying to achive is:


|06-06-2020|EUR|AAAAA|30-01-2020 |110|

The other records will only be grouped when both orders have Yes or OrderPaid
Many thanks in advance!

[A S MANN]

Create a Aggregate Query with Cliente Group By , Date Group By , OrderPaid Condition "Yes", Amount As Sum

[CJ_London]

don't think that will work - client BBBBB yes will be included, think the OP wants to exclude it because the other BBBBB record is No

suggest you need two queries, one for yes, one for no, then left join the yeses to the nos and only include where the nos is null

SELECT yeses.*
FROM 
    (SELECT Date, Cliente, PurchaseDate, sum(Amount) as Amt WHERE OrderPaid='Yes' GROUP BY  Date, Cliente, PurchaseDate) AS Yeses
    LEFT JOIN  
    (SELECT Date, Cliente, PurchaseDate, sum(Amount) as Amt WHERE OrderPaid='No' GROUP BY  Date, Cliente, PurchaseDate) AS Noes
    ON Yeses.Date = Noes.Date AND Yeses.Cliente = Noes.Cliente AND Yeses.PurchaseDate=Noes.PurchaseDate
WHERE Noes.Date IS Null

There may be more efficient ways of doing the Noes, but you get the idea

perhaps this would suffice

(SELECT Date, Cliente, PurchaseDate WHERE OrderPaid='No') AS Noes

[hillvalley45]

Many thanks to you both for your prompt reply!
A S MANN unfortunately your suggestion does not work because it will include other orders that have yes and no.

Ajax your suggestion solves the problem!!!