Jump to content

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


shashib

Recommended Posts

 id | mid | pid | owgh | nwgh |1    3     12     1.5   0.62    3     12     1.5   0.33    3     14     0.6   0.44    3     15     1.2   1.15    4     16     1.5   1.06    4     17     2.4   1.27    3     19     3.0   1.4
Select mid , COUNT(distinct pid) as cpid , SUM(nwgh) as totalnwgh from test GROUP BY mid

sqlfiddle : link of below result with above query

mid cpid totalnwgh3     4      3.84     2      2.2

But above i need one more column that's as below : **totowgh**

mid cpid totalnwgh totowgh3    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 columnthat'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.2SEE WHAT I MEANS : sqlfiddle.com/#!9/2a53c/6

Edited by shashib
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...