SQL query for counting records where count = 1 and Value <50-CodePudding

I have a table named NETWORK. Within this table I have Card, Date and Value. I would like to count all Distinct Card where Value is <= 50 group by Year and Month.

Scenario: if a Card has 10 transaction in a month and All Values are less than 50 then it should be counted once (distinct count), otherwise if one of the Value is greater than 50 then it should not be considered.

Sample Data:

 1. Card    Date    Value
443138XXXXXX0977    2020-01-13  42
443138XXXXXX0977    2020-01-04  50
443138XXXXXX0977    2020-01-22  43
443138XXXXXX0977    2020-02-22  29
524417XXXXXX7034    2020-01-10  38
524417XXXXXX7034    2020-01-07  35
443138XXXXXX4095    2020-01-28  17
443138XXXXXX4095    2020-01-02  37
443138XXXXXX4095    2020-01-27  55
471365XXXXXX0755    2021-03-10  69
471365XXXXXX0756    2021-03-10  22

Thanks.

CodePudding user response：

SELECT 
   t1.Card,
   COUNT(*)
FROM Table t1
WHERE Value <= 50 AND NOT EXISTS(SELECT 1 FROM Table t2 WHERE t1.Card=t2.Card AND t2.Value>50)
GROUP BY 
   t1.Card,
   Year(t1.Date), Month(t1.Date)
HAVING Count(*)=10

WHERE will filter all roews where values is <= 50 and only those rows where a values >50 does not exists
HAVING will filter the grouped record for a count of 10

CodePudding user response：

It should be almost exactly as you stated it:

Select count(distinct Card)
From Network
Where Value<=50