Counting number of grouped rows in mysql

Question

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

Table 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

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

The query returns

Table 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

Answer 1

Try this simple query without a sub-query:

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

See this SQLFiddle

Answer 2

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