Return Zero If No Record Is Found

Return zero if no record is found

You could:

SELECT COALESCE(SUM(columnA), 0) FROM my_table WHERE columnB = 1
INTO res;

This happens to work, because your query has an aggregate function and consequently always returns a row, even if nothing is found in the underlying table.

Plain queries without aggregate would return no row in such a case. COALESCE would never be called and couldn't save you. While dealing with a single column we can wrap the whole query instead:

SELECT COALESCE( (SELECT columnA FROM my_table WHERE ID = 1), 0)
INTO res;

Works for your original query as well:

SELECT COALESCE( (SELECT SUM(columnA) FROM my_table WHERE columnB = 1), 0)
INTO res;

More about COALESCE() in the manual.

More about aggregate functions in the manual.

More alternatives in this later post:

• How to return a value from a function if no value is found

SQL query - Return 0 for the column if no record found

Try below query:

SELECT A.Customer, ISNULL(B.Address, 0), 
ISNULL(B.[Number of lines],0), ISNULL(B.Date, '30-05-2022') Date
FROM
(
 SELECT DISTINCT Customer 
 FROM table_name
) A
LEFT JOIN table_name B
ON A.Customer = B.Customer
AND B.Date = '30-5-2022'

This will output all the customers present in the table. You can filter the customers with WHERE clause in the end of the above query based on your requirement.

dbfiddle Link

SQL-Query return zero count if no records are found

Taking a look at the documentation, you can use ISNULL:

ISNULL ( check_expression , replacement_value )

SELECT ISNULL(COUNT(comment), 0)) AS total
FROM dbo.omment
WHERE resp = MMColParam2 

SQL select return 0 if no records found, else return value

You can use this

SELECT ISNULL(( SELECT TOP 1 
   [avail]
   FROM [table1]
   where [name] = 'abc'
   order by [datetime] desc), 0) AS [avail]

return 0 in select count when no record found

Just use:

SELECT max(id) as id, count(*)
FROM table1
WHERE reference_id = 300000009798620

Return 0 as SUM result if no records are found

If there's a chance your activities table doesn't have an activity listed on a particular day, then fa06's trick won't work out. A simple way to cover that case is to add some zero records on before you do your sum:

SELECT DATE(d), SUM(c)
FROM (
  SELECT  a.end_time as d, a.calories as c
  FROM activities a
  JOIN user_activities uc ON uc.activity_id = a.id
  WHERE uc.user_id = 1
  AND DATE(a.end_time) >= CURRENT_DATE - INTERVAL 6 DAY
  UNION ALL
  SELECT CURRENT_DATE, 0
  UNION ALL
  SELECT CURRENT_DATE - INTERVAL 1 DAY, 0
  UNION ALL
  SELECT CURRENT_DATE - INTERVAL 2 DAY, 0
  UNION ALL
  SELECT CURRENT_DATE - INTERVAL 3 DAY, 0
  UNION ALL
  SELECT CURRENT_DATE - INTERVAL 4 DAY, 0
  UNION ALL
  SELECT CURRENT_DATE - INTERVAL 5 DAY, 0
  UNION ALL
  SELECT CURRENT_DATE - INTERVAL 6 DAY, 0
) z
GROUP BY DATE(d)

fa06's method relies on there being one record every day in the activities table. It's a valid way to approach the problem, but you haven't been specific about what data these tables contain (hint: when posting a db question, do include sample data from each table)

With this method we do our lookup as normal, but we also generate 7 fake rows with dates from the last 7 days and a 0 calorie count. When added onto real calorie counts, these are fat free ;) and when there is no data for a particular day, these stand alone to provide the 0

If you want more days, consider moving to a row generating pattern. MySQL doesn't have row generators like other DBs, but the simplest trick is to create
variable, init it with 0 then increment and use it upon every row returned from a table with at least 30 rows:

SELECT CURRENT_DATE - INTERVAL (@row := @row + 1) DAY as dt, 0 as cal
FROM activities t, (SELECT @row := -1) r
LIMIT 30

The theory behind this is: the table has at least 30 rows, @row variable is inited to -1, and exists session-wide for the query. As rows are pulled out and returned, @row is incremented and then returned (so it's 0, 1, 2.. ) and this increasing count is used to subtract 0, 1, 2 etc days off of the current data, giving us a sequence of dates for the past 30 days

SELECT DATE(d), SUM(c)
FROM (
  SELECT  a.end_time as d, a.calories as c
  FROM activities a
  JOIN user_activities uc ON uc.activity_id = a.id
  WHERE uc.user_id = 1
  AND DATE(a.end_time) >= CURRENT_DATE - INTERVAL 29 DAY
  UNION ALL

  SELECT CURRENT_DATE - INTERVAL (@row := @row + 1) DAY as dt, 0 as cal
  FROM activities t, (SELECT @row := -1) r
  WHERE @row < 30
) z
GROUP BY DATE(d)

Note that I wasn't able to test either of these queries; the second might have some minor syntax error. If it turns out not to work, and the error is nontrivial/something you cant fix let me know.

Debugging:

Run each of these queries in isolation. I don't know how many rows this will produce:

  SELECT  a.end_time as d, a.calories as c
  FROM activities a
  JOIN user_activities uc ON uc.activity_id = a.id
  WHERE uc.user_id = 1
  AND DATE(a.end_time) >= CURRENT_DATE - INTERVAL 29 DAY

And this one should produce 30 rows. If it doesn't it will be because you didn't use a table with at least 30 rows:

  SELECT CURRENT_DATE - INTERVAL (@row := @row + 1) DAY as dt, 0 as cal
  FROM activities t, (SELECT @row := -1) r
  LIMIT 30

Edit:

Fixed the bug with this - LIMIT was being applied after the union; this was giving undesired results

MySql count () to return 0 if no records found

Move the criteria in the WHERE clause to the ON clause of the join:

SELECT c.cla_name, COUNT(p.cla_name)
FROM clase c
LEFT JOIN products p
    ON p.cla_name = c.cla_name AND
       p.product_type = 29
GROUP BY c.cla_name;

Because the WHERE filter is happening before the join, your current query removes the D name record from the clase table before the join happens.

Return a value if no rows are found in Microsoft tSQL

SELECT CASE WHEN COUNT(1) > 0 THEN 1 ELSE 0 END AS [Value]

FROM Sites S

WHERE S.Id = @SiteId and S.Status = 1 AND 
      (S.WebUserId = @WebUserId OR S.AllowUploads = 1)

---

Related Topics