awestover89 awestover89 - 1 month ago 5
MySQL Question

MySQL GROUP BY column ignorning specific value

I have a query on a table of phone calls that uses GROUP BY to show only unique caller ids. The problem is that if a caller has caller id blocking their caller id shows up as "Unknown" and the client doesn't want all Unknowns to be summed up together. So basically, instead of just

GROUP BY caller_id
I need to somehow do something like
GROUP BY caller_id IF caller_id != 'Unknown'


Is this even possible? I'd like to avoid doing all the group processing in PHP if at all possible.

Answer

You can do something like:

SELECT caller_id FROM phone_calls WHERE caller_id != 'Unknown' GROUP BY caller_id;

or consider DISTINCT - in most cases it is faster - if you have index created on caller_id the performance is usually the same, but if not DISTINCT is better. If you need :

... show only unique caller ids ..

but maybe for your case (doing aggregation or something similar) you are not able to use it but just in case:

SELECT DISTINCT caller_id FROM phone_calls WHERE caller_id != 'Unknown';

-- EDIT AFTER discussion in comments

SELECT * FROM callers;                                                                                                                                                                                             
+----+-----------+-----------+                                                                                                                                                                                                        
| id | caller_id | call_time |                                                                                                                                                                                                        
+----+-----------+-----------+                                                                                                                                                                                                        
|  1 | abc       |        24 |                                                                                                                                                                                                        
|  2 | abc       |        16 |                                                                                                                                                                                                        
|  3 | xyz       |        10 |                                                                                                                                                                                                        
|  4 | xyz       |        10 |                                                                                                                                                                                                        
|  5 | Unknown   |        11 |                                                                                                                                                                                                        
|  6 | Unknown   |        12 |                                                                                                                                                                                                        
|  7 | Unknown   |        13 |                                                                                                                                                                                                        
|  8 | xyz       |         1 |                                                                                                                                                                                                        
|  9 | abc       |        10 |                                                                                                                                                                                                        
+----+-----------+-----------+   

SELECT caller_id, SUM(call_time) FROM callers 
WHERE caller_id != 'Unknown' 
GROUP BY caller_id;                                                                                                                     
+-----------+----------------+                                                                                                                                                                                                        
| caller_id | SUM(call_time) |                                                                                                                                                                                                        
+-----------+----------------+                                                                                                                                                                                                        
| abc       |             50 |                                                                                                                                                                                                        
| xyz       |             21 |                                                                                                                                                                                                        
+-----------+----------------+                                                                                                                                                                                                        

SELECT caller_id, SUM(call_time) FROM callers
GROUP BY caller_id;                                                                                                                                                  
+-----------+----------------+                                                                                                                                                                                                        
| caller_id | SUM(call_time) |                                                                                                                                                                                                        
+-----------+----------------+                                                                                                                                                                                                        
| abc       |             50 |                                                                                                                                                                                                        
| Unknown   |             36 |                                                                                                                                                                                                        
| xyz       |             21 |                                                                                                                                                                                                        
+-----------+----------------+                                                                                                                                                                                                        

SELECT caller_id, SUM(call_time) as total_time FROM callers 
WHERE caller_id != 'Unknown' 
GROUP BY caller_id 
UNION 
SELECT caller_id, call_time FROM callers 
WHERE caller_id = 'Unknown';                             
+-----------+------------+                                                                                                                                                                                                             
| caller_id | total_time |                                                                                                                                                                                                             
+-----------+------------+                                                                                                                                                                                                             
| abc       |         50 |                                                                                                                                                                                                             
| xyz       |         21 |                                                                                                                                                                                                             
| Unknown   |         11 |                                                                                                                                                                                                             
| Unknown   |         12 |                                                                                                                                                                                                             
| Unknown   |         13 |                                                                                                                                                                                                             
+-----------+------------+                                                                                                                                                                                                             

SELECT caller_id, SUM(call_time) as total_time FROM callers
GROUP BY caller_id, 
      (case when caller_id = 'Unknown' 
       AND id is not null 
       then id end
       );                                                                   
+-----------+------------+                                                                                                                                                                                                             
| caller_id | total_time |                                                                                                                                                                                                             
+-----------+------------+                                                                                                                                                                                                             
| abc       |         50 |                                                                                                                                                                                                             
| Unknown   |         11 |                                                                                                                                                                                                             
| Unknown   |         12 |                                                                                                                                                                                                             
| Unknown   |         13 |                                                                                                                                                                                                             
| xyz       |         21 |                                                                                                                                                                                                             
+-----------+------------+