CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012sql-server-2014sql-server-2016
Glory Raj
Glory Raj

Reputation: 17691

sql group by with count to know the status

Hi all i have two tables like this one is subscripiton having columns below like this ..

Id  SubscriptionKey  
 1   subkey1
 2   subkey2
 3   subKey3

another table is guardkey having columns like below

 id     key    SubscriptionKey   IsConverted
  1     key1     subkey1           True
  2     key2     subkey1           true
  3     key3     subkey2           true
  4     Key4     subkey2           false
  5     key5     subkey2           true
  6     key6     subKey1           false
  7     key7     subkey3           true
  8     key8     subkey3           true

how can we find out whether the all keys related to particular subkey is converted or not ..

I am binding this data to gridview and i need to disable the particular row if all keys for that particular subkey is converted

Could any one please help on this how to check whether all keys are converted for particular key..

Many thanks in advance..

I am using sql server..

Expected output is like this 

 subscriptionkey 
      subkey1
      subkey2

Upvotes: 0

Views: 632

Answers (3)

Pawan Kumar
Pawan Kumar

Reputation: 2011

Please try this-

--NOT CONVERTED SubscriptionKeys

SELECT [id], [key], [SubscriptionKey], [IsConverted]
FROM 
(
    SELECT * , COUNT(CASE WHEN IsConverted = 'True' THEN 1 END) OVER (PARTITION BY SubscriptionKey) a
             , COUNT(*) OVER (PARTITION BY SubscriptionKey) b     
    FROM guardkey
)x WHERE a = b

id          key  SubscriptionKey IsConverted
----------- ---- --------------- -----------
7           key7 subkey3         true
8           key8 subkey3         true
(2 rows affected)

--CONVERTED SubscriptionKeys

SELECT [id], [key], [SubscriptionKey], [IsConverted]
FROM 
(
    SELECT * , COUNT(CASE WHEN IsConverted = 'True' THEN 1 END) OVER (PARTITION BY SubscriptionKey) a
             , COUNT(*) OVER (PARTITION BY SubscriptionKey) b     
    FROM guardkey
)x WHERE a < b

id          key  SubscriptionKey IsConverted
----------- ---- --------------- -----------
1           key1 subkey1         True
2           key2 subkey1         true
6           key6 subKey1         false
3           key3 subkey2         true
4           Key4 subkey2         false
5           key5 subkey2         true


(6 rows affected)

Upvotes: 1

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8033

This will be the simple approach for archiving the above

DECLARE @T1 TABLE
(
    Id INT,
    SubscriptionKey VARCHAR(50)
)

DECLARE @T2 TABLE
(
    Id INT,
    [key] VARCHAR(50),
    SubscriptionKey VARCHAR(50),
    IsConverted BIT
)

INSERT INTO @t1
VALUES(1,'SubKey1'),
(2,'SubKey2'),
(3,'SubKey3')

INSERT INTO @T2
VALUES
(1,'key1','subkey1',1),
(2,'key2','subkey1',1),
(3,'key3','subkey2',1),
(4,'Key4','subkey2',0),
(5,'key5','subkey2',1),
(6,'key6','subKey1',0)

SELECT
    *
    FROM @T1 t1
        WHERE EXISTS(
            SELECT 1 FROM @T2 T2 WHERE T2.SubscriptionKey = t1.SubscriptionKey AND IsConverted = 1
        )
        AND NOT EXISTS(
            SELECT 1 FROM @T2 T2 WHERE T2.SubscriptionKey = t1.SubscriptionKey AND IsConverted = 0
        )

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can use the following query:

SELECT SubscriptionKey
FROM guardkey
GROUP BY SubscriptionKey
HAVING COUNT(*) <> COUNT(CASE WHEN IsConverted = 'True' THEN 1 END)

HAVING clause use COUNT function twice:

  • The first time to count all records of the group (COUNT(*))
  • The second time to conditionally count the 'converted' records of the group.

If these two counts are different then the subkey is not converted and its value is returned by the query.

Demo here

Edit: To get the rest of the fields from subscription table you can use the above query as a derived table:

SELECT s.*
FROM subscription AS s
JOIN (
    SELECT SubscriptionKey
    FROM guardkey
    GROUP BY SubscriptionKey
    HAVING COUNT(*) <> COUNT(CASE WHEN IsConverted = 'True' THEN 1 END)
) AS t ON s.SubscriptionKey = t.SubscriptionKey

Upvotes: 3

Related Questions