CODEWITHSUNDEEP
sqlcountgroup-by
concept1483166
concept1483166

Reputation: 39

Select where Count of 1 field is greater than a value and count of another is less than a value

I am trying to return all instances in a Customers table where the statustype= 'dc' then for those results, the count on FC is > 1 and the Count on Address1 is 1.

IE:

FC    Address1
111    abc
111    cde
432    qqq
432    qqq

I need the 111 FC results back because their address1 is different. But I don't need the 432 FC results back because there is more than 1 Address for that FC

 SELECT *
  FROM Customers
  where FC IN( select FC from Customers where StatusType= 'dc'
  group by FC having COUNT(FC) > 1 and COUNT(Address1) < 2

)
  order by FC, Address1

I also tried = 1 instead of < 2

Upvotes: 0

Views: 920

Answers (3)

Pரதீப்
Pரதீப்

Reputation: 93734

Try using Distinct COUNT

SELECT *
FROM   Customers
WHERE  FC IN(SELECT FC
             FROM   Customers
             WHERE  StatusType = 'dc'
             GROUP  BY FC
             HAVING Count(DISTINCT Address1) > 1)
ORDER  BY FC,
          Address1

Upvotes: 0

jpw
jpw

Reputation: 44891

If you want the details about the FCs that have more than one unique address then this query will give you that:

select c.* from customers c
join (
    select FC
    from customers
    where statustype = 'dc'
    group by fc having count(distinct Address1) > 1
) a on c.FC = a.FC

Upvotes: 1

JustAPup
JustAPup

Reputation: 1780

You will need to group by Address1 also.

SELECT *
FROM Customers
where FC IN( select FC from Customers where StatusType= 'dc'
group by FC, Address1 having COUNT(FC) > 1 and COUNT(Address1) < 2)
order by FC, Address1

Upvotes: 0

Related Questions