2 GROUP BY WITH DISTINCT，SUM，COUNT：PHP MySQL [英] 2 GROUP BY WITH DISTINCT, SUM , COUNT: PHP MySQL

问题描述

参考： GROUP BY WITH HAVING（DISTINCT）：PHP，MYSQL < a>

我得到了答案（感谢@coytech），但我还需要一个列：

每个：

  id |中| 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 
  

我得到了答案

选择中间，
COUNT（distinct pid）as cpid，
SUM（nwgh）as totalnwgh from test GROUP BY mid

sqlfiddle： http ：//sqlfiddle.com/#！9 / 45e68 / 2

  mid cpid totalnwgh 
 3 4 3.8 
 4 2 2.2 
  

但上面我还需要一个列，如下： strong> totowgh

  mid cpid totalnwgh totowgh 
 3 4 3.8 6.3（DISTINCT值为每个pid列）
 4 2 2.2 3.9 
  

其中totowgh = 6.3由DISTINCT值根据pid列

这是中= 3有数5，但是对于中= 3是不同的pid = 4同样的方式distinctowgh = 6.3对于mid = 3和不同的pid。 >

因为pid = 12是计数1次，因此，

1.5 + 0.6 + 1.2 + 3 = 6.3是根据pid的DISTINCT值）

请注意：我需要owgh值作为每个不同的pid或组由pid ..因为如果我替换值的owgh 0.6与1.5那么它将是5.7而不是7.2，但是owgh 0.6的值属于pid = 14而不是pid = 12，因此owgh的总计变化...但我需要是7.2

看看我的意思： sqlfiddle.com/#!9/2a53c/6 p>

解决方案

确定，根据你的后续评论这是我想出的。首先获得 owgh 的分组，然后用主查询执行 JOIN 。查看演示提示 http://sqlfiddle.com/#!9/a56e4/1

  SELECT t.mid`，
 COUNT（distinct t.`pid`）AS countmid，
 SUM（t.`nwgh`）AS totalnwgh，
 xx.`totowgh` 
 FROM test t JOIN（
 select mid，sum（owgh）as totowgh 
 from（
 select distinct mid，owgh from test）x 
 group by mid）xx ON t.mid` = xx.`mid` 
 GROUP BY t.`mid`; 
  

这将导致

  mid $ total $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $
Ref : GROUP BY WITH HAVING ( DISTINCT ) : PHP , MYSQL

I got answer (thanks @coytech) but I need one more column in it:

as per:
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
I got answer 

  
Select mid , 
        COUNT(distinct pid) as cpid  , 
        SUM(nwgh) as totalnwgh  from test  GROUP BY mid
sqlfiddle : http://sqlfiddle.com/#!9/45e68/2
mid  cpid       totalnwgh
3      4          3.8
4      2          2.2 
But above I need one more column that's as below : totowgh
mid cpid        totalnwgh  totowgh
3      4          3.8        6.3 (DISTINCT value as per pid column)
4      2          2.2        3.9
where totowgh = 6.3 come by DISTINCT value as per pid column

That's mid = 3 has count 5 but distinct pid  = 4 for mid=3 same way "distinct" owgh = 6.3 for mid=3 and distinct pid.

As pid=12 is count 1 time hence,

1.5 + 0.6 + 1.2 + 3 = 6.3 ( please not this is as per DISTINCT value of pid )

Please note : i need owgh value as per distinct pid or group by pid .. because if i replace value of owgh 0.6 with 1.5 then it will be 5.7 instead of 7.2 but value of owgh 0.6 belong to pid = 14 and not pid = 12 hence totalcount of owgh change ...but i need is 7.2

SEE WHAT I MEANS  : sqlfiddle.com/#!9/2a53c/6
解决方案
OK, per your followup comment this is what I came up with. First get the grouping of the distinct owgh and then perform a JOIN with main query. See a demo fiddle http://sqlfiddle.com/#!9/a56e4/1
SELECT t.`mid` , 
COUNT(distinct t.`pid`) AS countmid , 
SUM(t.`nwgh`) AS totalnwgh,
xx.`totowgh`
FROM test t JOIN (
select mid, sum(owgh) as totowgh 
from (
select distinct mid, owgh from test) x
group by mid) xx ON t.`mid` = xx.`mid`
GROUP BY t.`mid`;
It will result in
mid     countmid    totalnwgh   totowgh
3          4           3.8        6.3
4          2           2.2        3.9


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