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
从上面我想要 nwgh 的中间和总和及其相应的总数.id 例如:mid=3 或 mid=4但使用 DISTINCT pid 但请注意 nwgh 的总和不应为 DISTINCT
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
因此我的结果如下:
mid | countmid | totalnwgh
3 4 (DISTINCT value) 3.8 (no DISTINCT it take both value of pid =12)
4 2 2.2
在上面的结果 mid = 3 有计数 4 因为 pid = 12 重复，因此它的 DISTINCT 但 nwgh 不应该是 DISTINCT ，它的总数
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
我尝试过的
Select mid , COUNT(mid) as countmid , SUM(nwgh) as totalnwgh from test where mid = 3
GROUP BY mid HAVING count(DISTINCT pid)
推荐答案
如果我明白你想要什么，你只需要在你的 COUNT 中做一个 distinct.
If I understand what you want, you just have to do a distinct in your COUNT.
你可以试试这个:
SELECT mid ,
COUNT(distinct pid) as countmid ,
SUM(nwgh) as totalnwgh
FROM test
GROUP BY mid
如果你想试试这个 sqlfiddle:http://sqlfiddle.com/#!9/45e68/2
Try this sqlfiddle if you want : http://sqlfiddle.com/#!9/45e68/2
相关文章