sql - 如何在 impala 中使用 distinct

Question

您好，我正在尝试查询表中的不同位置。

这是我的查询。

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    |

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

Answer 1

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

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