php - 选择一个不同的值计数及其自身的值 1 查询

Question

我正在尝试从我的数据库中接收不同的成本，以及该不同成本的计数。我得到了“不同的成本”，但没有得到运行下一个流程所需的“成本”。有人可以帮我连同计数一起返回 DISTINCT COST 本身吗？

 $sql="SELECT COUNT(DISTINCT COST) AS COSTQ FROM STATS WHERE PURCHASEID = ".$purchaseid;
       $result = $mysqli->query($sql);

        if ($result->num_rows > 0) {
            // output data of each row
            while($row = $result->fetch_assoc()) {
                $costcoun = $row['COSTQ'];
                $cost = $row['COST']; // I have already tried ['DISTINCT COST'] which i had no luck with either.
              // This is where I run my next process
                  }
                 } else {
                      echo "0 results";
                 }

Answer 1

group by 子句正是为这种情况设计的——它将查询分解为不同的值，并对每个组分别应用聚合函数:

SELECT   cost, 
         COUNT(*) AS costq -- No need for distinct, GROUP BY handles it
FROM     stats
WHERE    purchaseid = 'something'
GROUP BY cost