How to group COUNTs for different queries in one?

Question

How to group COUNTs for different queries in one?

Navigation

#1 by (5 votes)

4

I need to do a 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 this same search without having to do all these querys ?

mysql sql sql-select

asked by anonymous 24.11.2017 / 15:41

1 answer

Why is JSON not considered a markup language? And why is XML? Difference hashmap and arraylist

score 5 · Accepted Answer

Yes you can unify them in a single query, for this you will use 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