MySQL 计算mysql中分组行的数量

Counting number of grouped rows in mysql

提问by alphy

In a table xyz I have a row called components and a labref row which has labref number as shown here

在表 xyz 中，我有一行称为 components 和 labref 行，其中 labref 编号如下所示

Table xyz

表 xyz

labref             component
NDQA201303001          a
NDQA201303001          a
NDQA201303001          a
NDQA201303001          a
NDQA201303001          b
NDQA201303001          b
NDQA201303001          b
NDQA201303001          b
NDQA201303001          c
NDQA201303001          c
NDQA201303001          c
NDQA201303001          c

I want to group the components then count the rows returned which equals to 3, I have written the below SQL query but it does not help achieve my goal instead it returns 4 for each component

我想对组件进行分组，然后计算返回的行数，等于 3，我编写了以下 SQL 查询，但它无助于实现我的目标，而是为每个组件返回 4

SELECT DISTINCT component, COUNT( component ) 
FROM `xyz`
WHERE labref = 'NDQA201303001'
GROUP BY component

The query returns

查询返回

Table xyz

表 xyz

labref         component   COUNT(component)       
NDQA201303001   a           4
NDQA201303001   b           4
NDQA201303001   c           4

What I want to achieve now is that from the above result, the rows are counted and 3 is returned as the number of rows, Any workaround is appreciated

我现在想要实现的是，根据上述结果，计算行数并返回 3 作为行数，任何解决方法表示赞赏

采纳答案by Kshitij

You need to do -

你需要做——

SELECT
    COUNT(*)
FROM
    (
        SELECT
            DISTINCT component
        FROM
            `multiple_sample_assay_abc`
        WHERE
            labref = 'NDQA201303001'
    ) AS DerivedTableAlias

---

---

You can also avoid subquery as suggested by @hims056 here

您还可以按照 @hims056此处的建议避免子查询

回答by hims056

Try this simple query without a sub-query:

试试这个没有子查询的简单查询：

SELECT COUNT(DISTINCT component) AS TotalRows
FROM xyz
WHERE labref = 'NDQA201303001';

See this SQLFiddle

看到这个 SQLFiddle

回答by Mohd Abdul Mujib

Why not use num_rows.

为什么不使用num_rows.

If you do it using this method, You don't have to modify the query in any way.

如果您使用此方法进行操作，则无需以任何方式修改查询。

if ($result = $mysqli->query("SELECT DISTINCT component, COUNT( component ) 
    FROM `xyz`
    WHERE labref = 'NDQA201303001'
    GROUP BY component")){

    /* determine number of rows result set */
    $row_cnt = $result->num_rows;

    printf("Result set has %d rows.\n", $row_cnt);

    /* close result set */
    $result->close();
}