CODEWITHSUNDEEP
sqlsql-server-2005
user6102644
user6102644

Reputation:

How to get number of false in every column of a table?

I have a table say T_myTable it has 5 columns and all have some values either true or false.

--------------------------------
col1 | col2 | col3 | col4 | col5
--------------------------------
true | false|false|true |false
false| true |false|false|false
true | false|false|true |false
false| false|false|true |false

I want to get result as:-

col1 | col2 | col3 | col4 | col5
--------------------------------
2    | 3    |4     |1     |4

Where these numbers here are numbers of false. Also true and false are varchar.

Upvotes: 3

Views: 108

Answers (3)

Maciej Los
Maciej Los

Reputation: 8591

One of the most interesting way is to:

  1. unpivot data to be able to filter data
  2. pivot data again to get count of 'false'

Check this:

SELECT [col1],[col2],[col3],[col4],[col5]
FROM (
    SELECT MyVal, ColName
    FROM (
        SELECT *
        FROM T_myTable
        ) AS pvt
    UNPIVOT(MyVal FOR ColName IN ([col1],[col2],[col3],[col4],[col5])) AS unpvt
    WHERE MyVAl = 'false'
    ) As DT
PIVOT (COUNT(MyVal) FOR ColName IN ([col1],[col2],[col3],[col4],[col5])) AS PT

Result:

col1    col2    col3    col4    col5
2       3       4       1       4

Upvotes: 0

diiN__________
diiN__________

Reputation: 7666

Try this:

SELECT SUM(CASE col1 WHEN 'false' THEN 1 ELSE 0 END),
    SUM(CASE col2 WHEN 'false' THEN 1 ELSE 0 END),
    SUM(CASE col3 WHEN 'false' THEN 1 ELSE 0 END),
    SUM(CASE col4 WHEN 'false' THEN 1 ELSE 0 END),
    SUM(CASE col5 WHEN 'false' THEN 1 ELSE 0 END)
FROM T_myTable

Upvotes: 0

Kim
Kim

Reputation: 825

This:

SELECT SUM(CASE WHEN Col1 = 'false' THEN 1 ELSE 0 END) AS Col1
    , SUM(CASE WHEN Col2 = 'false' THEN 1 ELSE 0 END) AS Col2
    , SUM(CASE WHEN Col3 = 'false' THEN 1 ELSE 0 END) AS Col3
    , SUM(CASE WHEN Col4 = 'false' THEN 1 ELSE 0 END) AS Col4
    , SUM(CASE WHEN Col5 = 'false' THEN 1 ELSE 0 END) AS Col5
FROM T_myTable

Upvotes: 2

Related Questions