If a Query Return Empty Row I Need to Get Its as Zero Value,How Can Check It in SQL

If a query return empty row i need to get its as zero value,How can check it in Sql?

I suspect your best bet here is to use a LEFT OUTER join instead of an INNER JOIN, and use ISNULL; the LEFT OUTER means that you'll keep the row even when it doesn't match anything, and the ISNULL lets you choose a value to use in that scenario; so:

Update TABLE_A
Set  TABLE_A.Form_8 =(Case When TABLE_B.Form_Type ='8' Then  ISNULL(TABLE_B.Invoice_NO, 0) End)
From TABLE_A
Left Outer Join (Select Max(Invoice_NO) as Invoice_NO,Form_Type, Counter_Name From Sales_Master
Group By Counter_Name,Form_Type
) TABLE_B on  TABLE_A.Counter_Name = TABLE_B.Counter_Name and 

TABLE_B.Form_Type ='8'

Getting SELECT to return zero in both cases of NULL and EMPTY row in single SQL query

If you want the result for only one LineNumber value, as the code suggests, it's trivial. Just remove the GROUP BY LineNumber. An aggregation without group by means that the result will be exactly one row, no matter if we have 0 or a million rows, before the aggregation:

SELECT  ISNULL(Sum(convert(dec,DeliveryPaymentExtras.ExtendedText)),0) AS ExtendedText
FROM    DeliveryPaymentExtras
WHERE   (LineNumber =21) ;

If you want results for multiple values (for example if you had: WHERE LineNumber IN (21, 34, 55) GROUP BY LineNumber), then it's not straightforward. One way is:

SELECT  v.LineNumber,
        ISNULL(Sum(convert(dec,d.DeliveryPaymentExtras.ExtendedText)),0) 
            AS ExtendedText
FROM    ( VALUES (21),(34),(55)
        ) AS v (LineNumber)
    LEFT JOIN DeliveryPaymentExtras AS d
        ON d.LineNumber = v.LineNumber
GROUP BY v.LineNumber ;

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 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 

Return 0 when result is empty

Maybe you wanted this (I'm keeping obvious syntax and spelling errors that I can't really correct without more information):

SELECT NumberOfAccedentInYear = ISNULL
(
  (SELECT COUNT(AccedentId)
   FROM         Accident
   GROUP BY DriverId, YEAR(AccedentDate)
   HAVING     (DriverId =@DriverId)<3))
   , 0
);

For anyone curious about the COALESCE vs ISNULL discussion, and why I changed my answer to use ISNULL, @kanav rightly pointed out that COALESCE is more expensive. COALESCE evaluates the subquery twice, as I explained here: https://stackoverflow.com/a/10669660/61305

how to return result as NULL if the SQL query return zero values in SQL

Use EXISTS like so:

-- returns NULL when it doesn't exist
SELECT  CASE WHEN EXISTS ( SELECT   1 AS val
                           WHERE    1 = 2 ) THEN 1
             ELSE NULL
        END RESULT;

-- returns something else, in this case 1 when it does exist    
SELECT  CASE WHEN EXISTS ( SELECT   1 AS val
                           WHERE    1 = 1 ) THEN 1
             ELSE NULL
        END RESULT;

EXISTS (Transact-SQL)

Specifies a subquery to test for the existence of rows.

A sample with data:

CREATE TABLE #test ( id INT );

INSERT  INTO #test
        ( id )
VALUES  ( 1 ),
        ( 2 ),
        ( 3 );

-- returns data
IF ( EXISTS ( SELECT    *
              FROM      #test
              WHERE     id > 0 ) )
    SELECT  *
    FROM    #test
    WHERE   id > 0;
ELSE
    SELECT  NULL;

 -- returns NULL 
 IF ( EXISTS ( SELECT    *
              FROM      #test
              WHERE     id > 5 ) )
    SELECT  *
    FROM    #test
    WHERE   id > 5;
ELSE
    SELECT  NULL AS Result;


DROP TABLE #test;     

How to Have an empty row if query result couldn't find anything with one assigned value under one column?

Since you don't have the table for users, and you have a huge case/when expression... You really SHOULD create the users table! It would be a nightmare to maintain the 2 large when/case expressions correctly!

If you still prefer to opt out from creating users table, just like Sami suggested, you should create an inline view and OUTER JOIN it to your main table. Select statement similar to the one below should do it.

NB: I've used syntax that is more familiar to me, but that should work
in SQL Server as well.

select b.client_name,
       sum(a.credit_used) total_usage,
       sum(a.debit) total_amount,
       b.nickname
  from MyTable a
       right outer join
       (select 123 account_id, 'John' client_name, 'J' nickname union all
        select 234, 'Doe','D') b
       on
       (a.account = b.account_id)
 where a.year = 2015
 group by b.client_name, b.nickname

How to textually indicate that there are no results in a SQL SELECT?

IF ((SELECT COUNT(*) FROM PS_MASTER_ITEM_EC EC WHERE EC.INV_ITEM_ID <> '' AND EC.INV_ITEM_ID IS NOT NULL) > 0)
SELECT EC.INV_ITEM_ID FROM PS_MASTER_ITEM_EC EC WHERE EC.INV_ITEM_ID <> '' AND EC.INV_ITEM_ID IS NOT NULL
ELSE
SELECT 'NO ITEMS IN STAGING TABLES'

First line will calculate how many non empty rows there are in the table
second line will return a 1 column table with all the IDs that were not NULL or '' if any is found at all
4th row will return the default message if the table was actually empty

How to check if a query returns a null value and add it to a label text?

The problem with your actual code is that even if the where condition don't return any record you still have a row in your table that contains a NULL. So your test is always true. You can test this simply copying your query in Sql Server Management Studio and running it.

Instead you could use ExecuteScalar to get the result

Dim cmd As SqlCommand = New SqlCommand("select sum(Minutes)/60 as 'Ore Logate' 
                                        from [dbo].[UserActivity]
                                        where team = 'Xaver'  
                                          and username like '%alnecula%' 
                                          and Convert(date,DateCreated) = Convert(date,getdate())", Con)
Dim result = cmd.ExecuteScalar()
If Not IsDBNull(result) Then 
    Txtgvlad.Text = "Logged Hours: " & result.ToString
Else
    Txtgvlad.Text = "You haven't logged any hours"
End If

Consider also that this query could return zero (but not a NULL) and, in this case the IF condition will be true. If you want to write the false message even in case of zero returned then you need to add a AndAlso condition to the If

If Not IsDBNull(result) AndAlso result.ToString <> "0" Then 

How to get Postgres to return 0 for empty rows

Please refer to the below script.

SELECT *
FROM 
    (SELECT date(created_at) AS created_at,
         COUNT(id) AS total_orders,
         SUM(total_price) AS total_price,
         SUM(taxes) AS taxes,
         SUM(shipping) AS shipping,
         AVG(total_price) AS average_order_value,
         SUM(total_discount) AS total_discount,
         SUM(total_price - taxes - shipping - total_discount) AS net_sales
    FROM orders
    WHERE shop_id = 43
        AND orders.active = true
        AND orders.created_at >= '2022-07-20'
        AND orders.created_at <= '2022-07-26'
    GROUP BY  date (created_at)
UNION
SELECT dates AS created_at,
         0 AS total_orders,
         0 AS total_price,
         0 AS taxes,
         0 AS shipping,
         0 AS average_order_value,
         0 AS total_discount,
         0 AS net_sales
FROM generate_series('2022-07-20', '2022-07-26', interval '1 day') AS dates
WHERE dates NOT IN 
    (SELECT created_at
    FROM orders
    WHERE shop_id = 43
        AND orders.active = true
        AND orders.created_at >= '2022-07-20'
        AND orders.created_at <= '2022-07-26' ) ) a
ORDER BY  created_at::date desc;

There is one sample for your reference.
Sample

I got your duplicate test cases at my side. The root cause is created_at field (datattype:timestamp), hence there are duplicate lines.

Below script is correct for your request.

SELECT *
FROM 
    (SELECT date(created_at) AS created_at,
         COUNT(id) AS total_orders,
         SUM(total_price) AS total_price,
         SUM(taxes) AS taxes,
         SUM(shipping) AS shipping,
         AVG(total_price) AS average_order_value,
         SUM(total_discount) AS total_discount,
         SUM(total_price - taxes - shipping - total_discount) AS net_sales
    FROM orders
    WHERE shop_id = 43
        AND orders.active = true
        AND orders.created_at >= '2022-07-20'
        AND orders.created_at <= '2022-07-26'
    GROUP BY  date (created_at)
UNION
SELECT dates AS created_at,
         0 AS total_orders,
         0 AS total_price,
         0 AS taxes,
         0 AS shipping,
         0 AS average_order_value,
         0 AS total_discount,
         0 AS net_sales
FROM generate_series('2022-07-20', '2022-07-26', interval '1 day') AS dates
WHERE dates NOT IN 
    (SELECT date (created_at)
    FROM orders
    WHERE shop_id = 43
        AND orders.active = true
        AND orders.created_at >= '2022-07-20'
        AND orders.created_at <= '2022-07-26' ) ) a
ORDER BY  created_at::date desc;

Here is a sample that's same with your side. Link

---

Related Topics