Stack Overflow
Stack Overflow

Questions tagged [coalesce]

COALESCE is a SQL function that returns the first non-NULL expression among its arguments. COALESCE() is ANSI standard and may be used instead of vendor-specific alternatives such as ISNULL(), NVL() or IFNULL().

Return the first non-NULL argument. The COALESCE(expression1, expression2... expressionN) function is variety of the CASE expression.

Examples

Consider the expression: 

CASE
   WHEN (expression1 IS NOT NULL) THEN expression1
   WHEN (expression2 IS NOT NULL) THEN expression2
   ...
   ELSE expressionN

It is equivalent to 

COALESCE(expression1, ... expressionN);

Another usage:

SELECT
  field1,
  COALESCE(SUM(customers), 0), -- if the SUM is NULL, it would be calculated as 0
  field3,
FROM
  ...

References

932 questions
9
votes
5 answers

MySQL: how to use COALESCE

Say I have the following table: TABLE: product =============================================================================== | product_id | language_id | name | description …
StackOverflowNewbie
  • 39,403
  • 111
  • 277
  • 441
9
votes
3 answers

Union query distinct on one column

I would like results from the second query to override results from the first query: SELECT "panel_restaurants_restaurant"."id", "panel_restaurants_restaurant"."name", "panel_restaurants_restaurant"."logo", …
Marek M.
  • 3,799
  • 9
  • 43
  • 93
9
votes
6 answers

SQL Return Null if One Column is Null (Opposite of COALESCE())

In advance, I would like to say thanks for the help. This is a great community and I've found many programming answers here. I have a table with multiple columns, 5 of which contain dates or null. I would like to write an sql query that essentially…
Rick Rodriguez
  • 119
  • 1
  • 1
  • 8
9
votes
2 answers

Using COALESCE in SQL view

I need to create a view from several tables. One of the columns in the view will have to be composed out of a number of rows from one of the table as a string with comma-separated values. Here is a simplified example of what I want to…
kateroh
  • 4,382
  • 6
  • 43
  • 62
8
votes
5 answers

Combine rows / concatenate rows

I'm looking for an Access 2007 equivalent to SQL Server's COALESCE function. In SQL Server you could do something like: Person John Steve Richard SQL DECLARE @PersonList nvarchar(1024) SELECT @PersonList = COALESCE(@PersonList + ',','') +…
NakedBrunch
  • 48,713
  • 13
  • 73
  • 98
8
votes
6 answers

Coalesce pairs of variables within a dataframe based on a regular expression

I want to use dplyr::coalesce to find the first non-missing value between pairs of variables in a dataframe containing multiple pairs of variable. The goal is to create a new dataframe with now only one copy for each pair of variable (a coalesce…
Antoine Lachance
  • 93
  • 4
8
votes
2 answers

NHibernate QueryOver order by first non-null value (coalescing)

What I'm trying to come up is something that's expressed like this: var result = Session.QueryOver().OrderBy(f => f.UpdatedAt ?? f.CreatedAt); Sure enough, this doesn't work. Rough equivalent of this in T-SQL is ... order by…
Anton Gogolev
  • 113,561
  • 39
  • 200
  • 288
8
votes
6 answers

Coalesce vs empty string concatenation

My coworker is new to C# and didn't know about the coalesce operator. So, I saw him write a line of code like this: string foo = "" + str; The idea being that if str is null, this expression would return an empty string. Of course, that could be…
Jason Baker
  • 192,085
  • 135
  • 376
  • 510
8
votes
1 answer

NHibernate QueryOver Coalesce a property to another property

Consider this silly domain: namespace TryHibernate.Example { public class Employee { public int Id { get; set; } public string Name { get; set; } } public class WorkItem { public int Id { get; set; } …
Sergei Tachenov
  • 24,345
  • 8
  • 57
  • 73
8
votes
1 answer

SQL Query: Hierarchical Coalesce

I have a table that defines a hierarchy: Create Table [example] ( id Integer Not Null Primary Key, parentID Integer Null, largeData1 nVarChar(max) Null, largeData2 nVarChar(max) Null); -- largeData3...n also…
Chris Nielsen
  • 14,731
  • 7
  • 48
  • 54
8
votes
1 answer

"invalid input syntax for type numeric" for entering "emptyness"

I have a table with three columns using the NUMERIC type. However, two of them have nothing (ie. empty). Here it's the code: CREATE TABLE profiles( ID SMALLINT, FID SMALLINT, SURVEY VARCHAR(100), PROFILE VARCHAR(100), TYPE VARCHAR(100), SOURCE…
Gery
  • 8,390
  • 3
  • 22
  • 39
8
votes
5 answers

SQL - Ugly combination of GROUP BY and COALESCE

I have a table with data similar to the following: [ID], [State], [foo], [DateCreated], [DateUpdated] The longer I work on this, the uglier my SQL is getting, which tells me I'm probably doing something wrong. What I want is a unique list of each…
Scott K
  • 83
  • 1
  • 1
  • 3
7
votes
1 answer

How to display a default value when no match found in a query?

I want to display a default message when there is no data obtained from a query. For example Let us take a query select empname from employee where id = 100 If no data matches this search i want to get Unavailable as a result or the required…
Hariharbalaji
  • 2,498
  • 8
  • 36
  • 51
7
votes
13 answers

How to concatenate strings and commas in SQL Server?

I'm relatively new to MSSQL, so sorry if the question might sounds trivial. I want to concatenate multiple fields with a delimiter ,. However, when the field is empty, the extra , will be included in the result string as well. So is there an easy…
roxrook
  • 13,511
  • 40
  • 107
  • 156
7
votes
1 answer

PySpark aggregation function for "any value"

I have a PySpark Dataframe with an A field, few B fields that dependent on A (A->B) and C fields that I want to aggregate per each A. For example: A | B | C ---------- A | 1 | 6 A | 1 | 7 B | 2 | 8 B | 2 | 4 I wish to group by A , present any of B…
Dimgold
  • 2,748
  • 5
  • 26
  • 49
1 2 3
…
62 63