I have table in database like below:

and i want to count how many 'AB' that userABC get.

So the result that i want as per below

anyone know the query or keyword for this situation? Thank you in advance!!

CodePudding user response：

You can try something like this with sum and case statements:

select sum(case when ColA = 'AB' then 1 else 0 end
  case when ColB = 'AB' then 1 else 0 end
  case when ColC = 'AB' then 1 else 0 end
  case when ColD = 'AB' then 1 else 0 end
  case when ColE = 'AB' then 1 else 0 end
  case when ColF = 'AB' then 1 else 0 end
  case when ColG = 'AB' then 1 else 0 end) from table_name

Fiddle

CodePudding user response：

Unpivot the table using VALUES table value constructor and count the AB's. If you want to get the count for each row, the following statement is an option:

-- Sample data
SELECT *
INTO TestTable
FROM (VALUES 
   (1, 'AA', 'AB', 'AA', 'AA', 'AA', 'AB', 'AA', 'userABC')
) t (id, ColA, ColB, ColC, ColD, ColE, ColF, ColG, USER_ID)

-- T-SQL
SELECT 
   t.USER_ID,
   (
   SELECT COUNT(*)
   FROM (VALUES (t.ColA), (t.ColB), (t.ColC), (t.ColD), (t.ColE), (t.ColF), (t.ColG)) v (Col)
   WHERE Col = 'AB'
   ) AS CountAB
FROM TestTable t
WHERE (t.USER_ID = 'userABC')

If the table has more than one row for one USER_ID, you need to group the rows:

-- Sample data
SELECT *
INTO TestTable
FROM (VALUES 
   (1, 'AA', 'AB', 'AA', 'AA', 'AA', 'AB', 'AA', 'userABC'),
   (2, 'AA', 'AB', 'AA', 'AA', 'AA', 'AB', 'AA', 'userABC'),
   (3, 'AA', 'AB', 'AA', 'AA', 'AA', 'AB', 'AA', 'userABC')
) t (id, ColA, ColB, ColC, ColD, ColE, ColF, ColG, USER_ID)

-- T-SQL
SELECT t.USER_ID, COUNT(a.Col) AS CountAB
FROM TestTable t
OUTER APPLY (VALUES 
   (t.ColA), (t.ColB), (t.ColC), (t.ColD), (t.ColE), (t.ColF), (t.ColG)
) a (Col)
WHERE (t.USER_ID = 'userABC') AND (a.Col = 'AB')
GROUP BY t.USER_ID

CodePudding user response：

You could go from wide to tall format, and then aggregate by user:

WITH cte AS (
    SELECT USER_ID, ColA AS val FROM yourTable UNION ALL
    SELECT USER_ID, ColB FROM yourTable UNION ALL
    SELECT USER_ID, ColC FROM yourTable UNION ALL
    SELECT USER_ID, ColD FROM yourTable UNION ALL
    SELECT USER_ID, ColE FROM yourTable UNION ALL
    SELECT USER_ID, ColF FROM yourTable UNION ALL
    SELECT USER_ID, ColG FROM yourTable
)

SELECT USER_ID, COUNT(*) AS [count AB]
FROM cte
WHERE val = 'AB'
GROUP BY USER_ID;

CodePudding user response：

You may try something like this

Select Id, User_Id, ((Select Isnull(count(Id),0) from [Table] where ColA 
like'AB' and Id = A.Id)   
(Select Isnull(count(Id),0) from [Table] where ColB = 'AB' and Id = A.Id)   
(Select Isnull(count(Id),0) from [Table] where ColC = 'AB' and Id = A.Id)   
(Select Isnull(count(Id),0) from [Table] where ColD = 'AB' and Id = A.Id)   
(Select Isnull(count(Id),0) from [Table] where ColE = 'AB' and Id = A.Id)   
(Select Isnull(count(Id),0) from [Table] where ColF = 'AB' and Id = A.Id)   
(Select Isnull(count(Id),0) from [Table] where ColG = 'AB' and Id = A.Id) 
) as Result
from [Table] A