1

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

  • 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? – tombom Sep 12 '15 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 – user3209031 Sep 12 '15 at 11:09
4

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.