CODEWITHSUNDEEP
sqloracle-databaseplsql
venkat
venkat

Reputation: 5738

Show Zero if there is no record count - ORACLE SQL query

The below Oracle query gives if there are any different errors with error_message and Serial_num.

If there is ZERO or No Different error count instead of showing Blank/Null result. How can i see the output like this? I tried with NVL(error_message,0) and COALESCE (Sum(total),0) but not getting the desired output.

Expected output: 

1   Different Errors:       0

Oracle SQL Query:

SELECT 
  1 as Index_Num, 
  CONCAT('Different Errors:  ', error_message || '# ' || serial_num), 
  SUM(total) 
FROM ( 
  SELECT error_message, serial_num, COUNT(*) total 
  FROM Table1 
  WHERE error_message NOT LIKE '%INVALID%' 
  GROUP BY error_message, serial_num
) 
GROUP BY error_message, serial_num

Upvotes: 1

Views: 2704

Answers (3)

ravioli
ravioli

Reputation: 3833

D'oh! Looks like I took too long. Here's another option for posterity:

SELECT 
  1, 
  CONCAT(
    'Different Errors:  ', 
    CASE 
      WHEN src.error_message IS NULL THEN '' 
      ELSE src.error_message || ' # ' || src.serial_num 
    END
  ) Summary,
  COALESCE(src.total, 0) AS total
FROM dual -- Get a seed row (in case there are no rows in error table)
LEFT JOIN (
  SELECT error_message, serial_num, COUNT(*) total 
  FROM Table1 
  WHERE error_message NOT LIKE '%INVALID%' 
  GROUP BY error_message, serial_num
) src ON 0=0

SQL Fiddle

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

It is not exactly what you are asking for, but might prove useful. You can easily add a row with the total number of errors, using grouping sets:

SELECT 1 as Index_Num, 
       ('Different Errors:  ' || error_message || '# ' || serial_num), 
       COUNT(*) as total 
FROM Table1 
WHERE error_message NOT LIKE '%INVALID%' 
GROUP BY GROUPING SETS ( (error_message, serial_num), () );

Alas, this produces the summary row even when there are errors. It occurs to me that you might find this useful.

Upvotes: 0

forpas
forpas

Reputation: 164194

Create a CTE for the subquery and use UNION ALL with NOT EXISTS to cover the case that the CTE does not return any rows:

WITH cte AS (
  SELECT error_message, serial_num, COUNT(*) total 
  FROM Table1 
  WHERE error_message NOT LIKE '%INVALID%' 
  GROUP BY error_message, serial_num
)
SELECT 
  1 as Index_Num, 
  CONCAT(
    'Different Errors:  ', 
    list_agg(error_message || '# ' || serial_num) within group (order by error_message)
  ), 
  SUM(total) 
FROM cte
UNION ALL
SELECT 1, 'Different Errors:  ', 0
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM cte)

Upvotes: 1

Related Questions