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
Try this simple query without a sub-query:
SELECT COUNT(DISTINCT component) AS TotalRows
FROM xyz
WHERE labref = 'NDQA201303001';