CODEWITHSUNDEEP
sqlsql-servercount
ezles88
ezles88

Reputation: 179

SQL Count how many column that have specific value in specific user

I have table in database like below:

id ColA ColB ColC ColD ColE ColF ColG USER_ID
1 AA AB AA AA AA AB AA userABC

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

So the result that i want as per below

count AB
2

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

Upvotes: 1

Views: 59

Answers (4)

Zhorov
Zhorov

Reputation: 29943

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

Upvotes: 2

Yat Fei Leong
Yat Fei Leong

Reputation: 819

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

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521073

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;

Upvotes: 1

Zakaria
Zakaria

Reputation: 4806

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

Upvotes: 2

Related Questions