-1

The following query does take the most and least district by num of customers. I tried to divide the task into grouping first by count of customers, then limiting by 1.

But why is the union not working?

SELECT     
    A.district,
    COUNT(DISTINCT C.customer_id) cust_cnt
FROM 
    address A   
GROUP BY 
    A.district 
ORDER BY
    cust_cnt 
LIMIT 1

UNION

SELECT     
    A.district,
    COUNT(DISTINCT C.customer_id) cust_cnt
FROM 
    address A   
GROUP BY 
    A.district 
ORDER BY
    cust_cnt desc 
LIMIT 1
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ERJAN
  • 23,696
  • 23
  • 72
  • 146
  • Tag your question with the database you are using. – Gordon Linoff Aug 17 '21 at 01:35
  • It has to do with the difference between `union` and `union all`. See https://stackoverflow.com/questions/49925/what-is-the-difference-between-union-and-union-all – Sam M Aug 17 '21 at 04:04
  • Also see https://dev.mysql.com/doc/refman/8.0/en/union.html and https://dev.mysql.com/doc/refman/8.0/en/union.html#union-order-by-limit for the order of operations on `order by` and `limit` when using a union. It gets to the meat of why you are getting unexpected results. – Sam M Aug 17 '21 at 04:08

1 Answers1

0

It is quite possible that you simply need parentheses:

(SELECT A.district,
        COUNT(DISTINCT C.customer_id) as cust_cnt      
 FROM address A  
 GROUP BY A.district 
 ORDER BY cust_cnt 
 LIMIT 1
) UNION ALL
(SELECT A.district,
        COUNT(DISTINCT C.customer_id) as cust_cnt  
 FROM address A  
 GROUP BY A.district 
 ORDER BY cust_cnt desc 
 LIMIT 1
);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • In adding the parenthesis, your answer also changed the OP's `union` to a `union all`. The functional difference between union and union all explains why his query did not return the expected results. – Sam M Aug 17 '21 at 04:05