sql - 如何在Impala中使用distinct

您好，我想查询表中的不同地区。

这是我的查询。

select distinct city,locality, avg_sqft from real_estate.re_search where city = 'bangalore'  AND locality != 'jayanagar';

结果

+-----------+--------------+----------+
| city      | locality     | avg_sqft |
+-----------+--------------+----------+
| bangalore | bannerghatta | 13500    |
| bangalore | kormangala   | 18000    |
| bangalore | kodipur      | 7000     |
| bangalore | kormangala   | 16000    |
| bangalore | horamavu     | 9000     |
| bangalore | bellandur    | 15500    |
| bangalore | kodipur      | 9000     |
| bangalore | madivala     | 12000    |
| bangalore | varthur      | 12000    |
| bangalore | kormangala | 13500    |
| bangalore | bellandur    | 13000    |
| bangalore | kodipur      | 11500    |
| bangalore | kormangala   | 14000    |

问题是我需要在结果中显示不同的位置。任何帮助将不胜感激。

最佳答案

通过使用COUNT和GROUP BY运算符，您应该能够在表格中获得城市中类加罗尔的本地性列的不同成员的列表:

SELECT city
      ,locality
      ,COUNT(locality)
FROM database.table
WHERE city = 'Bangalore'
GROUP BY city
        ,locality;