Jump to content
Sign in to follow this  
shashib

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

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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...