CODEWITHSUNDEEP
sqlsql-servert-sql
Andrew Bullock
Andrew Bullock

Reputation: 37406

TSQL: how to write this query?

I have a query which returns a result set which looks like this:

A   |   B
---------------------
1   |   a
2   |   a
1   |   b
1   |   c
3   |   d

A|B is unique in the results (i.e. there will only ever be one 1|a row)

I need to write two queries based on these results. The first:

given a value for A (say 1), count the number of Bs who only have a single row; with a 1 in the A column.

so in the above data set, id expect to be returned the value 2, because B values b and c only have one row each, where those rows have a 1 in the A column. d would not be counted because it corresponds to a 3 and a would not be returned because it has an additional row with a 2.

The second query is the opposite to the above.

Given a value for A (say 1) count the number of Bs who have 2 or more rows, where one of the rows has a 1 in the A column.

So in the above data id expect to be returned 1, because the B value a is the only one that has multiple rows, with one of them with a 1 in the A column.

Upvotes: 3

Views: 124

Answers (2)

Andriy M
Andriy M

Reputation: 77737

With CTEs and windowed aggregate functions, it could be done like this:

WITH
  sampledata (A, B) AS (
    SELECT 1, 'a' UNION ALL
    SELECT 2, 'a' UNION ALL
    SELECT 1, 'b' UNION ALL
    SELECT 1, 'c' UNION ALL
    SELECT 3, 'd'
  ),
  counted AS (
    SELECT
      A,
      B,
      COUNT(*) OVER (PARTITION BY B) B_count
    FROM sampledata
  )
SELECT
  COUNT(*)
FROM counted
WHERE A = 1
  AND B_count = 1  /* change to "> 1" to get the other result */

Upvotes: 3

Kirill Polishchuk
Kirill Polishchuk

Reputation: 56222

Use:

declare @t table(A int, B char(1))

insert @t values
(1, 'a'),
(2, 'a'),
(1, 'b'),
(1, 'c'),
(3, 'd')

declare @x int = 1

select COUNT(*)
from @t t
where t.A = @x
    and not exists(select 1 from @t t2 where t2.B = t.B and t2.A <> @x)

select COUNT(*)
from @t t
where t.A = @x
    and exists(select 1 from @t t2 where t2.B = t.B and t2.A <> @x)

Queries provide exactly wanted result:

-----------
2    

-----------
1

Upvotes: 6

Related Questions