SQL Group By 语句


使用示例

GROUP BY为我们提供了一种组合行和聚合数据的方法。

使用的数据来自我们在其中一些指南中使用的广告系列贡献数据。

以下SQL语句正在回答这个问题:“哪些候选人在2016年获得的捐款总额最大,但只有那些拥有超过2000万美元的捐款?”

以降序(DESC)顺序对此数据集进行排序会将总贡献最大的候选项放在列表顶部。

SELECT Candidate, Election_year, sum(Total_$), count(*)
FROM combined_party_data
WHERE Election_year = 2016
GROUP BY Candidate, Election_year -- this tells the DBMS to summarize by these two columns
HAVING sum(Total_$) > 20000000  -- limits the rows presented from the summary of money ($20 Million USD)
ORDER BY sum(Total_$) DESC; -- orders the presented rows with the largest ones first.
+--------------------------------------------------+---------------+-------------------+----------+
 | Candidate                                        | Election_year | sum(Total_$)      | count(*) |
 +--------------------------------------------------+---------------+-------------------+----------+
 | CLINTON, HILLARY RODHAM & KAINE, TIMOTHY M (TIM) |          2016 | 568135094.4400003 |      126 |
 | TRUMP, DONALD J & PENCE, MICHAEL R (MIKE)        |          2016 | 366853142.7899999 |      114 |
 | SANDERS, BERNARD (BERNIE)                        |          2016 |      258562022.17 |      122 |
 | CRUZ, RAFAEL EDWARD (TED)                        |          2016 | 93430700.29000005 |      104 |
 | CARSON, BENJAMIN S (BEN)                         |          2016 | 62202411.12999996 |       93 |
 | RUBIO, MARCO ANTONIO                             |          2016 |        44384313.9 |      106 |
 | BUSH, JOHN ELLIS (JEB)                           |          2016 |       34606731.78 |       97 |
 +--------------------------------------------------+---------------+-------------------+----------+
 7 rows in set (0.01 sec)

更多SQL教程

学习更多SQL教程