Counting number of grouped rows in mysql

alphy picture alphy · May 16, 2013 · Viewed 86.6k times · Source

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

Himanshu Jansari picture Himanshu Jansari · May 16, 2013

Try this simple query without a sub-query:

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

See this SQLFiddle