具有 distinct 和 sum 的 SQL 查询 [英] SQL query with distinct and sum

问题描述

我有以下 medleys 表，它结合了 colors、fruits 和 ratings:

I have the following medleys table that combines colors, fruits and ratings:

[medleys]
medley_id   |   color   |   fruit   |   rating
==============================================
1               red         apple       25
2               blue        pear        5
3               green       apple       12
4               red         apple       10
5               purple      kiwi        5
6               purple      kiwi        50
7               blue        kiwi        3
8               blue        pear        9

我正在尝试编写一个符合 ANSI 的 SQL 查询，它将组合每个唯一/不同的 color-fruit 对，并对每对的单独 rating值.因此，如果您在上面的表上运行查询，它将产生以下结果集:

I am trying to write an ANSI-compliant SQL query that will combine every unique/distinct color-fruit pair and sum each pair's individual rating values. Thus if you ran the query on the table above it would produce the following result sets:

[query]
color   |   fruit   |   sum
===========================
red         apple       35
blue        pear        14
blue        kiwi        3
green       apple       12
purple      kiwi        55

因此，查询发现表中有两个 red-apple 对，因此它为 red- 创建了一个结果-apple 对，并将它们的组成 ratings (25 + 10 = 35) 等相加.

Thus, the query sees there are two red-apple pairs in the table, and so it creates one result for the red-apple pair, and adds up their constituent ratings (25 + 10 = 35), etc.

我确定我需要选择 distinct 颜色/水果值，但不确定如何在同一级别/范围"聚合评分:

I am sure that I need to do a select for distinct color/fruit values, but not sure how to aggregate the ratings at the same "level/scope":

SELECT
    distinct(color, fruit), sum(rating)
FROM
    medleys

顺序无关紧要.color 和 fruit 是 VARCHAR(50)s 而 rating 是 INT.提前致谢！

Order doesn't matter. color and fruit are VARCHAR(50)s and rating is INT. Thanks in advance!

推荐答案

SELECT color, fruit, sum(rating)
FROM medleys
GROUP BY color, fruit

Distinct 用于选择不同的元素，仅此而已，而您想要聚合，为此您需要 GROUP BY 和聚合函数 (SUM).

Distinct is used to select distinct elements, nothing more, while you want to aggregate and for that you need GROUP BY and aggregation functions (SUM).

这篇关于具有 distinct 和 sum 的 SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！