Oracle SQL query to return 0 if particular data has count 0-CodePudding

My table looks something like that

So i write the SQL query to get data on 13-10-22 as

Source_name	time	hash	data
America	13-10-22	vfdgh	Any
Africa	12-10-22	hgffgh	any
America	13-10-22	hhhnn	any

SELECT source_name AS source, 
       COUNT(*) AS number_count
FROM mytable
WHERE time LIKE '-10-22%'
GROUP BY source_name

Output:

source	number_count
America	2

But I'm expecting

source	number_count
America	2
Africa	0

Please help.

CodePudding user response：

You can count conditionally with a CASE statement inside the SUM aggregate function as follows:

SELECT mytable.source_name AS source, 
       SUM(CASE WHEN mytable.time_ LIKE '-10-22%' THEN 1 ELSE 0 END) AS number_count
FROM mytable
GROUP BY mytable.source_name

Check the demo here.

CodePudding user response：

Others have well described why your query doesn't return what you expect. Here's another solution, using a CTE and COALESCE.

create table my_data (
  source_name varchar(20), 
  some_column varchar(20)
  );

Add your data

select *
from my_data

SOURCE_NAME	SOME_COLUMN
America	TTT
Africa	CC
America	TTT

with my_sources as (
  select distinct source_name
  from my_data
  )
select a.source_name, coalesce(count(b.source_name), 0) as number_count
from my_sources a
left join my_data b
  on a.source_name = b.source_name
 and b.some_column like '%T%' 
group by a.source_name

SOURCE_NAME	NUMBER_COUNT
America	2
Africa	0

fiddle here

CodePudding user response：

You're misunderstanding SQL order of operations. When you filter your table with WHERE time LIKE '-10-22%', you eliminate the Africa rows before the GROUP BY or SELECT operations happen. You need to LEFT JOIN your summary table back onto the original table:

WITH
    counts AS (
        SELECT 
            source_name, 
            COUNT(*) AS number_count 
        FROM 
            mytable 
        WHERE 
            time LIKE '-10-22%' 
        GROUP BY 
            source_name
        )

SELECT
    sources.source_name,
    ISNULL(counts.number_count, 0) AS number_count
FROM
    (SELECT DISTINCT source_name FROM mytable) sources
    LEFT JOIN counts ON sources.source_name = counts.source_name
ORDER BY
    number_count DESC,
    source_name
;