SQL Server - CONCAT + IF in VIEW

Question

I have a problem and I’ll try to explain the situation.

I have a client chart called ENTIDADES, there is an attribute called ID_GRUPOECONOMICO where this is a FK table GRUPOECONOMICO. I’m creating a VIEW to return the amount of customers in each economic group and the attribute GRPECON_DESCRIÇÃO (is the description of the economic group ex. BRONZE CONTRACT, SILVER CONTRACT, ETC).

So far I am running this query:

SELECT
   CONCAT('Total de ', COUNT(E.ID_GRUPOECONOMICO), ' cliente(s) ' + GP.GRPECON_DESCRICAO) AS 'QUANTIDADE CLIENTE POR GRUPO ECONOMICO' 
FROM
   dbo.ENTIDADES AS E 
   LEFT JOIN
      dbo.GRUPOECONOMICO AS GP 
      ON GP.ID_GRUPOECONOMICO = E.ID_GRUPOECONOMICO 
GROUP BY
   GP.GRPECON_DESCRICAO

When executing this query brings me the following information:

In this result is presented one of my problems, where in line 5 brings a total of 9 results that have no id_grupoeconomico assigned, that is, this as 0. I’m not able to put a condition to check if NULL and replaced by a message(SEM GRUPO ECONOMICO).
I need this information to come as:

Tota de 9 cliente(s) SEM GRUPO ECONOMICO

After this comes the bigger problem, how do I apply this in a VIEW because when I try to create it, it presents a problem with the CONCAT().

looking at some posts I touched on the query of VIEW to operate, however in the results, the line 5 comes as NULL(I believe it has to do with previous problem).

SELECT
   { fn CONCAT('Total de ', CAST(COUNT(E.ID_GRUPOECONOMICO) AS VARCHAR(10)) + ' clientes ' + GP.GRPECON_DESCRICAO) } AS [QUANTIDADE CLIENTE POR GRUPO ECONOMICO] 
FROM
   dbo.ENTIDADES AS E 
   LEFT OUTER JOIN
      dbo.GRUPOECONOMICO AS GP 
      ON GP.ID_GRUPOECONOMICO = E.ID_GRUPOECONOMICO 
GROUP BY
   GP.GRPECON_DESCRICAO

and the result:

My question is how to write the query to treat these entries with id_groupoeconimico = 0 and if solving the query, can be applied to the view as well?

Answer 1

You can use "CASE WHEN" to solve your problem in the first query, works as an IF:

SELECT
    CONCAT('Total de ', COUNT(E.ID_GRUPOECONOMICO), ' cliente(s) ' + CASE WHEN GP.GRPECON_DESCRICAO IS NULL THEN 'SEM GRUPO ECONOMICO' ELSE  GP.GRPECON_DESCRICAO END) AS 'QUANTIDADE CLIENTE POR GRUPO ECONOMICO' 
FROM dbo.ENTIDADES AS E 
LEFT JOIN
  dbo.GRUPOECONOMICO AS GP 
  ON GP.ID_GRUPOECONOMICO = E.ID_GRUPOECONOMICO 
GROUP BY
GP.GRPECON_DESCRICAO

Answer 2

The simplest way is to apply a ISNULL in the column GRPECON_DESCRIÇÃO:

SELECT      CONCAT('Total de ', CAST(COUNT(E.ID_GRUPOECONOMICO) AS VARCHAR), ' cliente(s) ' + ISNULL(GP.GRPECON_DESCRICAO, 'SEM GRUPO ECONOMICO')) AS 'QUANTIDADE CLIENTE POR GRUPO ECONOMICO' 
FROM        dbo.ENTIDADES       E 
LEFT JOIN   dbo.GRUPOECONOMICO  GP ON GP.ID_GRUPOECONOMICO = E.ID_GRUPOECONOMICO 
GROUP BY    GP.GRPECON_DESCRICAO

Don’t forget to make one CAST in E.ID_GRUPOECONOMICO because if it’s numerical type will give error in CONCAT.