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

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
``````

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

• What if the value of `owgh` is one time `1.5` and one time `1.4` for `mid = 3` and `pid = 12`? Which value should be chosen to calculate with? Commented Sep 12, 2015 at 11:06
• good question sir ....then both should be taken ...as my first preference is to see distinct value for owgh for pid = 12 .. repeated value should be seen with distinct pid..as said above ... owgh value 1.5 may again repeat in pid=14 then its should be treated different as its belong to two different pid Commented Sep 12, 2015 at 11:09

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
``````