CODEWITHSUNDEEP
postgresql
Kenzo_Gilead
Kenzo_Gilead

Reputation: 2439

Count except zeros

How can I count a number of record where zeros are not counted?

SELECT c.id AS Id
        ,c.A AS A
        ,sum(phtd.RealCol1) / 3600 AS RealCol1
        ,CAST(count(phtd.RealCol1) AS DECIMAL) AS RealCol2
    FROM ubdr
    INNER JOIN cfg c ON (ubdr.B = c.id)
    INNER JOIN per_historico phtd ON (
            phtd.B = ubdr.B
            AND ubdr.D = phtd.D
            )
    INNER JOIN per_t pt ON (phtd.T= pt.id)
    INNER JOIN per_p pa ON (pa.T= pt.id)
    WHERE 1 = 1
    GROUP BY 1
        ,2

I wrote this CAST(count(phtd.RealCol1) AS DECIMAL) AS RealCol2, but this count is counting zeros as well. How can I achieve counting records without counting zeros? I mean counting records distinct of zero

Upvotes: 0

Views: 57

Answers (2)

S-Man
S-Man

Reputation: 23676

demos:db<>fiddle

You can use conditional aggregation:

COUNT(phtd.RealCol1) FILTER (WHERE phtd.RealCol1 <> 0)

Instead of using FILTER clause you can use a conditional SUM using the CASE clause:

SUM(
   CASE WHEN phtd.RealCol1 = 0 THEN 0 ELSE 1 END
)

This can be optimized to:

SUM((phtd.RealCol1 <> 0)::int)

Upvotes: 2

labradore
labradore

Reputation: 313

Instead of CAST(count(phtd.RealCol1) AS DECIMAL) AS RealCol2, you can use SUM:

CAST(SUM( CASE WHEN phtd.RealCol1 > 0 THEN 1 ELSE 0 END ) AS DECIMAL) AS RealCol2

or, you can use FILTER:

CAST((COUNT(phtd.RealCol1) FILTER (WHERE phtd.RealCol1 > 0)) AS DECIMAL) AS RealCol2

Upvotes: 1

Related Questions