计算MySQL中分组行的数量 [英] Counting number of grouped rows in mysql

问题描述

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

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

表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

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

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

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

查询返回

表xyz

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

我现在要实现的是，根据以上结果，对行进行计数，并返回3作为行数，希望能找到任何解决方法

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

推荐答案

您需要做-

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

---

您也可以避免@ hims056在此处

这篇关于计算MySQL中分组行的数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！