GROUP BY WITH HAVING ( DISTINCT ) : PHP , MYSQL

问题:

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

From Above i want total count of mid AND SUM of nwgh with its resp. id ex: mid=3 or mid=4 but with DISTINCT pid but please note sum of nwgh should not be DISTINCT

Hence my result will be as below :

mid  | countmid            |   totalnwgh
3      4 (DISTINCT value)      3.8 (no DISTINCT it take both value of pid =12)
4      2                       2.2

in above result mid = 3 have count 4 beause pid = 12 is repeated hence its DISTINCT but nwgh should not be DISTINCT , its total count

what i have tried

Select mid , COUNT(mid) as countmid  , SUM(nwgh) as totalnwgh from test where mid = 3
GROUP BY mid HAVING count(DISTINCT pid)

回答1:

If I understand what you want, you just have to do a distinct in your COUNT.

You can try this :

SELECT mid , 
    COUNT(distinct pid) as countmid  , 
    SUM(nwgh) as totalnwgh 
FROM test 
GROUP BY mid 

Try this sqlfiddle if you want : http://sqlfiddle.com/#!9/45e68/2

回答2:

use this

SELECT a.`mid`, a.cnt AS countmid, b.s AS totalnwgh
FROM
    (SELECT `mid`, COUNT(DISTINCT pid) AS cnt FROM test GROUP BY 1) AS a INNER JOIN
    (SELECT `mid`, SUM(nwgh) AS s FROM test GROUP BY 1) b ON a.mid=b.mid