CODEWITHSUNDEEP
mysqlsqlcount
alphy
alphy

Reputation: 991

Counting number of grouped rows in mysql

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

Upvotes: 75

Views: 122250

Answers (6)

Nereare
Nereare

Reputation: 558

There is also:

SELECT `labref`, `component`, COUNT(*) as `count`
  FROM `xyz`
  WHERE labref = 'NDQA201303001'
  GROUP BY `component`;

Which should return:

labref component count
NDQA201303001 a 4
NDQA201303001 b 4
NDQA201303001 c 4

And, bonus points (I hope), no subqueries!

Based on this source.

Upvotes: 0

nojitsi
nojitsi

Reputation: 431

I found the solution. So, if you want to count quantity of groups, not quantity of elements in each group, and return duplicate value to every group record in result table, you should use OVER() clause on you'r count function.

So, for example above the solution would be

SELECT component, COUNT(*) OVER() as number_of_components FROM `xyz` 
WHERE labref = 'NDQA201303001' 
GROUP BY component

I suppose that works with any query that use GROUP BY, additional info, check in the link above.

Upvotes: 12

user3400085
user3400085

Reputation: 1

Select labref , component ,Count(*) as Counts From xyz Group by labref , component

Upvotes: -2

Kshitij
Kshitij

Reputation: 8614

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

Upvotes: 65

Mohd Abdul Mujib
Mohd Abdul Mujib

Reputation: 13908

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

Upvotes: 0

Himanshu
Himanshu

Reputation: 32602

Try this simple query without a sub-query:

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

See this SQLFiddle

Upvotes: 150

Related Questions