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
Best Answer
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: