GROUP BY WITH HAVING (DISTINCT) : PHP, MYSQL [英] GROUP BY WITH HAVING ( DISTINCT ) : PHP , MYSQL

问题描述

id | mid | pid | owgh | nwgh |
1    3      12    1.5    0.6
2    3      12    1.5    0.3
3    3      14    0.6    0.4
4    3      15    1.2    1.1
5    4      16    1.5    1.0
6    4      17    2.4    1.2 
7    3      19    3.0    1.4

从上面我想要 nwgh 的中间和总和及其相应的总数.id 例如:mid=3 或 mid=4但使用 DISTINCT pid 但请注意 nwgh 的总和不应为 DISTINCT

From Above i want total count of mid AND SUM of nwgh with its resp. id ex: mid=3 or mid=4 but with DISTINCT pid but please note sum of nwgh should not be DISTINCT

因此我的结果如下:

mid  | countmid            |   totalnwgh
3      4 (DISTINCT value)      3.8 (no DISTINCT it take both value of pid =12)
4      2                       2.2

在上面的结果 mid = 3 有计数 4 因为 pid = 12 重复，因此它的 DISTINCT 但 nwgh 不应该是 DISTINCT ，它的总数

in above result mid = 3 have count 4 beause pid = 12 is repeated hence its DISTINCT but nwgh should not be DISTINCT , its total count

我尝试过的

Select mid , COUNT(mid) as countmid  , SUM(nwgh) as totalnwgh from test where mid = 3
GROUP BY mid HAVING count(DISTINCT pid)

推荐答案

如果我明白你想要什么，你只需要在你的 COUNT 中做一个 distinct.

If I understand what you want, you just have to do a distinct in your COUNT.

你可以试试这个:

SELECT mid , 
    COUNT(distinct pid) as countmid  , 
    SUM(nwgh) as totalnwgh 
FROM test 
GROUP BY mid 

如果你想试试这个 sqlfiddle:http://sqlfiddle.com/#!9/45e68/2

Try this sqlfiddle if you want : http://sqlfiddle.com/#!9/45e68/2

这篇关于GROUP BY WITH HAVING (DISTINCT) : PHP, MYSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！