Counting number of grouped rows in mysql

mysql count(distinct group by)
mysql count group by
mysql count group by multiple columns
mysql count rows
mysql group by
mysql count(distinct with condition)
mysql count of each distinct value
php mysql count number of rows with same value

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

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

Counting number of grouped rows in mysql, You need to do - SELECT COUNT(*) FROM ( SELECT DISTINCT component FROM `multiple_sample_assay_abc` WHERE labref  MySQL COUNT() using multiple tables The following MySQL statement retrieves those rows from publisher table whose 'pub_id' in publisher table match the 'pub_id' in 'book_mast' table. A grouping operation is performed on pub_id column of publisher table by GROUP BY and then number of times pub_id exists in publisher table is counted by COUNT().

Try this simple query without a sub-query:

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

Getting Row Counts in MySQL (part 1), in a result set returned by a SELECT statement. MySQL COUNT() function with group by on multiple columns The following MySQL statement returns number of publishers in each city for a country. Grouping operation is performed on country and pub_city column with the use of GROUP BY and then COUNT() counts the number of publishers for each groups.

Why not use 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();
}

SQL COUNT() with GROUP by, How do I count the number of rows in a SQL group by? The COUNT() function allows you to count all rows or only rows that match a specified condition. The COUNT() function has three forms: COUNT(*), COUNT(expression) and COUNT(DISTINCT expression). COUNT(*) function. The COUNT(*) function returns the number of rows in a result set returned by a SELECT statement. The COUNT(*) returns the number of rows including duplicate, non-NULL and NULL rows. COUNT(expression)

MySQL COUNT - Counting Rows in a Table, The COUNT() function is an aggregate function that returns the number of rows in a table. The COUNT() function allows you to count all rows or only rows that match a specified condition. The COUNT() function has three forms: COUNT(*) , COUNT(expression) and COUNT(DISTINCT expression) . SELECT COUNT(t3.id) FROM t1, t2, t3 WHERE (associate t1,t2, and t3 with each other) GROUP BY t3.id However the GROUP BY changes the meaning of the COUNT, and instead I get a set of rows representing the number of unique t3.id values in each group. Is there a way to get a count for the total number of rows when I use a GROUP BY?

MySQL Tutorial, If you use the COUNT(*) function on groups returned with the GROUP BY clause, it will count the number of rows within each group, not the number of groups. I need to count the above records to get 1+1+1 = 3. Just remove the groupby??? – LorenVS Feb 28 '11 at 20:07. @LorenVS: But that would give me a count of the number of records in the table. I need number of records after the group by happens. – Chris Feb 28 '11 at 20:08. The group by doesn't change the number of rows though. 1 + 1 + 2 (in

MySQL 5.7 Reference Manual :: 3.3.4.8 Counting Rows, COUNT(*) counts the number of rows, so the query to count your animals looks The preceding query uses GROUP BY to group all records for each owner . SQL COUNT ( ) with group by and order by . In this page, we are going to discuss the usage of GROUP BY and ORDER BY along with the SQL COUNT() function. The GROUP BY makes the result set in summary rows by the value of one or more columns. Each same value on the specific column will be treated as an individual group.

Comments
  • You mean you want 3 as an output in this case?
  • select count(DISTINCT component) from xyz
  • @Kshitij - Why to use subquery when we can do it directly?
  • @hims056 not required.
  • hims056' solution is the better one - avoid subquerys if you're able to
  • Add alias after closing parenthesis.
  • The reason for this answer is that it will work when you need a having clause. The answer below relies on being able to rewrite the query without a having clause.
  • Of course this is the best solution, because it doesn't require a subquery.
  • Best Solution. This solution should be accepted as this is simpler and better approach
  • This will work if you don't have "GROUP BY". If query has "Group By" then all rows will have 1.
  • @JohnySkovdal - You've made a fiddle to answer a different question, so saying this answer "does not work" is incorrect. As your fiddle shows, this answer correctly reports the number of distinct values.
  • @JoelEnanodJr, Bala - You wouldn't use "Group By" with this answer; this answer is an alternative approach, that doesn't need (nor tolerate) "Group By" in the query. OP only used "Group By" as an attempt to get the answer they need. Combining "Group By" with "Count(Distinct)" is not meaningful; Remove "Group By" from your query.
  • Shorter code if nest selects: select count(1) from ( ... ) t; will yield row count of any query [with some limitations, specifically inner query must not have duplicate column names], where "..." is the query you are counting. For performance, can also change inner SELECT DISTINCT component, COUNT( component ) FROM to SELECT DISTINCT component FROM.