CODEWITHSUNDEEP
sqlpivot
Chris Hartland
Chris Hartland

Reputation: 11

SQL statement to count column entries

Given the data below,

TEST_SET_1   TEST_1     PASSED

TEST_SET_1   TEST_1     NOT COMPLETED

TEST_SET_1   TEST_1     PASSED

TEST_SET_1   TEST_1     FAILED

TEST_SET_1   TEST_1     FAILED

How do I format it using SQL to appear as below?

TEST SET    TEST      PASSED   FAILED   NOT COMPLETED

TEST_SET_1   TEST_1      2         2           1

Upvotes: 1

Views: 88

Answers (1)

Arion
Arion

Reputation: 31239

Something like this:

SELECT
    [TEST SET],
    [TEST],
    SUM(CASE WHEN [state]='PASSED' THEN 1 ELSE 0 END) AS PASSED,
    SUM(CASE WHEN [state]='FAILED' THEN 1 ELSE 0 END) AS FAILED,
    SUM(CASE WHEN [state]='NOT COMPLETED' THEN 1 ELSE 0 END) AS [NOT COMPLETED]
FROM
    table
GROUP BY
    [TEST SET],
    [TEST]

Or if you want to do it with a PIVOT and uses MSSQL 2005+. Then you can do it like this:

SELECT
    *
FROM
(
    SELECT
        [TEST SET],
        [TEST],
        [state],
        1 AS StaticNbr
    FROM
        table
) AS SorceTable
PIVOT
(
    SUM(StaticNbr)
    FOR [state] IN ([PASSED], [FAILED], [NOT COMPLETED])
) AS pvt

Upvotes: 2

Related Questions