Count(*) returning null

Asked

Viewed 177 times

-2

I have this query:

SELECT COUNT(*) FROM USER_IND_COLUMNS
     WHERE TABLE_NAME = UPPER('TABLE')
     AND COLUMN_NAME IN (UPPER('ANO'),UPPER('MES'),UPPER('DIA'),UPPER('HORA'))
     GROUP BY INDEX_NAME HAVING COUNT(*) = 4;

As there are no records, it should return 0 but does not return anything, how to return 0 if there are no occurrences.

sql oracle
  • 4

    You are explicitly selecting only those that have count equal to 4, but you expect it to be 0 in the result? It would be like taking only the white stockings of the wardrobe and wondering that there is no red in the middle.

  • @Does Andersoncarloswoss have a more elegant way of doing this? I need to test an if in this result.

  • First you need to describe exactly what you need to do. If you need results that have a value other than 4, simply remove such a condition. In fact, what’s the point of doing UPPER('TABLE'), UPPER('ANO'), etc? All strings are uppercase, so why use the function upper? It should not be together with the value that is variable?

  • 4

    @But it’s not about elegance, it’s about coherence. It could "solve" with coalesce, but how do you want it to result in zero if you explicitly say you only want it when the count equals 4?

4 answers

0

having functions as a kind of Where for the group elements, in which case there is no record with count equal to 4 respecting the grouping.

one way to do this would be by using sub-consumption:

select count(*) 
from (
    SELECT COUNT(*) contagem FROM USER_IND_COLUMNS
     WHERE TABLE_NAME = UPPER('TABLE')
     AND COLUMN_NAME IN (UPPER('ANO'),UPPER('MES'),UPPER('DIA'),UPPER('HORA'))
     GROUP BY INDEX_NAME 
) where contagem = 4;
  • 1

    I can’t even turn the result to 0 in this condition?

0

If we assume that your table has a primary key (or column with unique values), you can do something like:

SELECT      ISNULL(X.CONTADOR, 0) AS CONTADOR, UIC.*
FROM        USER_IND_COLUMNS UIC
LEFT JOIN   (
                SELECT      CODIGO
                        ,   COUNT(1) AS CONTADOR
                FROM        USER_IND_COLUMNS
                WHERE       TABLE_NAME = UPPER('TABLE')
                        AND COLUMN_NAME IN (UPPER('ANO'), UPPER('MES'), UPPER('DIA'), UPPER('HORA'))
                GROUP BY    INDEX_NAME 
                HAVING      COUNT(1) = 4
            ) X ON X.CODIGO = UIC.CODIGO

I used the column CODIGO as a table key.

0

Take off the having that is removing the return and puts the condition in itself select:

SELECT DISTINCT CASE WHEN COUNT(*) = 4 THEN 0 ELSE COUNT(*) END
FROM USER_IND_COLUMNS
WHERE TABLE_NAME = UPPER('TABLE')
  AND COLUMN_NAME IN (UPPER('ANO'),UPPER('MES'),UPPER('DIA'),UPPER('HORA'))
GROUP BY INDEX_NAME;
  • 1

    added the distinct to keep him from returning thousand results..

0

You can do it:

SELECT ISNULL(SELECT COUNT(*) FROM USER_IND_COLUMNS
     WHERE TABLE_NAME = UPPER('TABLE')
     AND COLUMN_NAME IN (UPPER('ANO'),UPPER('MES'),UPPER('DIA'),UPPER('HORA'))
     GROUP BY INDEX_NAME HAVING COUNT(*) = 4);0)

Browser other questions tagged sql oracle

You are not signed in. Login or sign up in order to post.