How to group Counts for different queries into one?

Question

I need to make an SQL query to count indications, I have the following querys:

SELECT 'FACEBOOK', COUNT(id) FROM `clientes` where indicacao like '%face%' 

SELECT 'Instagram', COUNT(id) FROM `clientes` where indicacao like '%insta%'

SELECT 'google', COUNT(id) FROM `clientes` where indicacao like '%google%'

SELECT 'panfleto', COUNT(id) FROM `clientes` where indicacao like '%panf%' 

SELECT 'indicacao', COUNT(id) FROM `clientes` where indicacao like '%ind%' 

SELECT 'radio', COUNT(id) FROM `clientes` where indicacao like '%radio%' 

SELECT 'outros',  COUNT(id) as total FROM `clientes` where indicacao not like '%radio%' and indicacao not like '%face%' and indicacao not LIKE '%insta%' and indicacao not like '%google%' and indicacao not like '%panf%' and indicacao not like '%ind%' and indicacao not like '%radio%'

My question is: I can do that same research without having to do all these querys?

Answer 1

Yes you can unify them in a single query, for that you will make use of CASE WHEN and GROUP BY.

GROUP BY:

With its use it is possible to group several records based on one or more columns of a table.

CASE WHEN:

The CASE expression is used to evaluate various conditions and return a unique value for each condition.

See the example below:

SELECT 
   CASE WHEN Indicacao NOT LIKE '%face%' 
        AND Indicacao NOT LIKE '%insta%'  
        AND Indicacao NOT LIKE '%google%' 
        AND Indicacao NOT LIKE '%panf%' 
        AND Indicacao NOT LIKE '%ind%'
        AND Indicacao NOT LIKE '%radio%' THEN 
    'Outros'
   ELSE
     Indicacao
   END AS 'Indicacao'
   , COUNT(ID) 
FROM clientes
GROUP BY 
  CASE WHEN Indicacao NOT LIKE '%face%' 
        AND Indicacao NOT LIKE '%insta%'  
        AND Indicacao NOT LIKE '%google%' 
        AND Indicacao NOT LIKE '%panf%' 
        AND Indicacao NOT LIKE '%ind%'
        AND Indicacao NOT LIKE '%radio%' THEN 
    'Outros'
   ELSE
     Indicacao
   END
ORDER BY ID;

Sqlfiddle - Example working online