May I know how can I construct the follow query in SQL Server?

user_1861864 0
Viewed 37
CREATE TABLE (
    A INT NOT NULL,
    B INT NOT NULL
)

A is an enumerated values of 1, 2, 3, 4, 5

B can be any values

I would like to count() the number of occurrence group by B, with a specific subset of A e.g. {1, 2}

Example:

A   B
1   7 *
2   7 *
3   7
1   8 *
2   8 *
1   9
3   9

When B = 7, A = 1, 2, 3. Good

When B = 8, A = 1, 2. Good

When B = 9, A = 1, 3. Not satisfy, 2 is missing

So the count will be 2 (when B = 7 and 8)

sql-server sql
3 Answers

If I've understood you correctly, we want to find B values for which we have both a 1 and a 2 in A, and then we want to know how many of those we have.

This query does this:

declare @t table (A int not null, B int not null)
insert into @t(A,B) values
(1,7),
(2,7),
(3,7),
(1,8),
(2,8),
(1,9),
(3,9)

select COUNT(DISTINCT B) from (
select B
from @t
where A in (1,2)
group by B
having COUNT(DISTINCT A) = 2
) t

One or both of the DISTINCTs may be unnecessary - it depends on whether your data can contain repeating values.

user_154980

If I understand correctly and the requirement is to find Bs with a series of As that doesn't have any "gaps", you could compare the difference between the minimal and maximal A with number of records (per B, of course):

SELECT   b
FROM     mytable
GROUP BY b
HAVING   COUNT(*) + 1 = MAX(a) - MIN(a)
user_24227760 
SELECT COUNT(DISTINCT B)  FROM TEMP T WHERE T.B NOT IN 
(SELECT B FROM
(SELECT B,A,
LAG (A,1) OVER (PARTITION BY B ORDER BY A) AS PRE_A
FROM Temp) K
WHERE K.PRE_A IS NOT NULL AND K.A<>K.PRE_A+1);
user_54655930
Related