CODEWITHSUNDEEP
sqlsql-server
yanglini
yanglini

Reputation: 3

Calculate the number of values ​in each field separately in SQL

I have this table like this:

Column A Column B Column C
B 1 w
B 2 e
A 1 p

I want to get result like this:

Column CountByValue
B 2
A 1
1 2
2 1
w 1
e 1
p 1

Is there any way to get the above result with SQL? Thanks.

Upvotes: 0

Views: 44

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

You can use APPLY, but you don't need a subquery:

SELECT V.[Column], COUNT(*)
FROM T CROSS APPLY
     (VALUES (T.ColumnA), (T.ColumnB), (T.ColumnC)
     ) v(Column)
GROUP BY V.[Column]

Upvotes: 1

PSK
PSK

Reputation: 17943

It can be done like following also using CROSS APPLY

SELECT V.[Column],COUNT(*) CountByValue
FROM MyTable T
CROSS APPLY (
    SELECT [Column]
    FROM (
        VALUES (T.ColumnA),(T.ColumnB),(T.ColumnC)) v([Column])
    ) v
GROUP BY V.[Column]

Upvotes: 2

Leszek Mazur
Leszek Mazur

Reputation: 2531

If values are distinct between columns you need UNION ALL:

SELECT "Column A" AS "Column", COUNT() AS "CountByValue"
FROM "MyTable"
GROUP BY "Column A"

UNION ALL

SELECT "Column B", COUNT()
FROM "MyTable"
GROUP BY "Column B"

UNION ALL

SELECT "Column C", COUNT()
FROM "MyTable"
GROUP BY "Column C"

Upvotes: -1

Related Questions