MYSQL 查询 SUM 和 DISTINCT? [英] MYSQL Query for SUM and DISTINCT?

问题描述

我有一个包含前五列的数据库，如下所示:

ID 名称 数量 价格 种类1 狗 2 5 A2 类 1 6 乙3 狗 2 5 C4 鸟 5 5 C

(狗的数量和价格总是一样的)

我想做的是类似的事情

选择 KIND、SUM(QUANTITY * PRICE) GROUP BY KIND WHERE DISTINCT NAME

这样我就能得到如下所示的内容:

<前>一个 10乙 6C 25

(消除重复的DOG.)

我知道我上面的语法大错特错——这似乎是解释我正在寻找什么样的东西的最有说服力的方式.

换句话说，我想去掉非不同的 NAMES，然后对其余部分求和.我似乎可以做一个，但不能同时做.

有什么想法吗?如果情况更糟，我可以将其作为 PHP 中的循环而不是单个 MYSQL 查询来执行.

解决方案

我不太清楚规则是什么，或者为什么你的表格采用这种格式(名称、数量、价格重复)，但这里有一个获得预期输出的方式.

选择种类，SUM(数量*价格)从(选择名称、数量、价格、最小(种类)种类从你的桌子按名称、数量、价格分组) t按种类分组

I have a database with the first five columns like this:

ID  NAME  QUANTITY   PRICE   KIND
1    Dog       2       5      A
2    Cat       1       6      B
3    Dog       2       5      C
4    Bird      5       5      C

(DOG QUANTITY and PRICE will always be the same)

What I want to do to is to something like

SELECT KIND, SUM(QUANTITY * PRICE) GROUP BY KIND WHERE DISTINCT NAME

So that I get something that looks like this:

A 10
B  6
C 25   

(The duplicate DOG is eliminated.)

I know my syntax above is grossly wrong -- it's just seems to be the most eloquent way of explaining what sort of thing I'm looking for.

In other words, I want to get rid of non-distinct NAMES then SUM the rest. I seem to be able to do one or the other but not both.

Any ideas? If worse comes to worst I can do it as a loop in PHP rather than as a single MYSQL query.

解决方案

I'm not really clear about either what the rules are or why your table is in that format (with repeated name, quantity,price) but here is one way of getting your expected output.

select kind, SUM(quantity*price)
from
(
SELECT name, quantity, price, min(kind) kind
FROM YourTable
group by name, quantity, price
) t
group by kind

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