2 GROUP BY WITH DISTINCT , SUM , COUNT : PHP MYQL

Question

Ref : https://stackoverflow.com/questions/32066097/group-by-with-having-distinct-php-mysql

I got answer (thanks coytech ) but i need one more column in its :

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 of owgh column as per distinct pid column

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 , column owgh value 1.5 is twice ...hence its 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 distinct 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

Answer 1

I believe you are asking for the sum of "distinct" values of owgh for each value of mid and pid - a different "granularity" of data.

e.g. for mid = 3 then add 1.5 + 0.6 + 1.2 + 3.0 = 6.3; only count the 1.5 once instead of twice. However if your record ids for the 1.5 were for 2 different pid values, then you would want to count the 1.5 twice.

One way you could do it is instead of querying the "test" table directly, calculate some of the aggregates beforehand:

Select mid,
COUNT(distinct pid) as cpid,
SUM(nwgh) as totalnwgh,
SUM(owgh) as totowgh    -- I added this one
from 
    (select mid, pid, sum(distinct owgh) as owgh, sum(nwgh) as nwgh from test group by mid, pid) as A
GROUP BY mid