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 ex：mid = 3或mid = 4
但带有DISTINCT pid，但请注意nwgh的总和不应该为DISTINCT

因此，我的结果如下：

  mid | countmid | totalnwgh 
 3 4（DISTINCT值）3.8（无DISTINCT它需要pid = 12的两个值）
 4 2 2.2 
  

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

我试过的东西

 选择mid，COUNT（mid）作为countmid，SUM（nwgh）作为来自test的totalnwgh，其中mid = 3 
 GROUP BY mid HAVING count（DISTINCT pid）
  pre> 
 
解决方案
如果我理解您的要求，您只需在 强> COUNT 即可。  
 
 
您可以试试这个： 
 
 
  SELECT mid，
 COUNT（distinct pid）作为countmid，
 SUM（nwgh）作为totalnwgh 
 FROM test 
 GROUP BY mid 
  
如果你愿意，可以试试这个sqlfiddle： http：// sqlfiddle .com /＃！9 / 45e68 / 2  
 
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
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 

Hence my result will be as below :
mid  | countmid            |   totalnwgh
3      4 (DISTINCT value)      3.8 (no DISTINCT it take both value of pid =12)
4      2                       2.2
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

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

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

You can try this :
SELECT mid , 
    COUNT(distinct pid) as countmid  , 
    SUM(nwgh) as totalnwgh 
FROM test 
GROUP BY mid 
Try this sqlfiddle if you want : http://sqlfiddle.com/#!9/45e68/2

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