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

You mean you want 3 as an output in this case? – Himanshu May 16 '13 at 10:13 — Himanshu, May 16 '13 at 10:13
select count(DISTINCT component) from `xyz` – ATR May 16 '13 at 10:19 — ATR, May 16 '13 at 10:19

Himanshu · Answer 1 · 2018-04-23T05:08:25.833

147

Try this simple query without a sub-query:

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

See this SQLFiddle

edited Apr 23 '18 at 05:08

answered May 16 '13 at 10:22

Himanshu

31,810
31
111
133

3

Of course this is the best solution, because it doesn't require a subquery. – Meloman Dec 21 '16 at 15:58
1

Best Solution. This solution should be accepted as this is simpler and better approach – Ayyaz Zafar Aug 25 '17 at 19:51
12

This will work if you don't have "GROUP BY". If query has "Group By" then all rows will have 1. – Bala Dec 20 '17 at 03:07
@hims056 Question is asked with "GROUP BY component" For example:: if we have data like `(id, component, labref) (1, 1, 'NDQA201303001'), (2, 1, 'NDQA201303001'), (3, 2, 'NDQA201303001'), (4, 2, 'NDQA201303001');` and then if we execute `SELECT COUNT(DISTINCT component) AS TotalRows FROM xyz WHERE labref = 'NDQA201303001' group by component` It results like TotalRows 1,1 – Bala Dec 20 '17 at 08:43
1

I'm on same scenario and @Bala is right, this solution wont work if query has "Group By". -1 – Joel Enanod Jr Jan 24 '18 at 15:50
@hims056 I concocted a fiddle here where it does not work: http://sqlfiddle.com/#!9/8295c9/2/1 - Only the 3rd SQL provides the correct result for: "How many distinct values occurs more than once?" – Johny Skovdal Apr 18 '18 at 18:34
It is a bit off topic from the question though, but still the answer I went looking for when I first found this answer. :) – Johny Skovdal Apr 18 '18 at 18:35
2

@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. – ToolmakerSteve Jan 20 '19 at 02:38
6

@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. – ToolmakerSteve Jan 20 '19 at 02:41
1

@ToolmakerSteve it's been a while, so I don't know my reasoning back then, and I think I might have mistaken some of the commenters as the OP. What I meant to say: If you query is more complex and `GROUP BY` is needed to narrow it down to the data you want to count, the fiddle provides an alternative that allows that: sqlfiddle.com/#!9/8295c9/2/1 - if it's not the case though, this answer is the better/simpler approach to pick. – Johny Skovdal Jan 21 '19 at 19:36
Note that rows with `component = NULL` will not be counted. – Magnar Myrtveit Aug 17 '21 at 09:18

score 61 · Accepted Answer · edited Aug 02 '19 at 16:57

61

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

edited Aug 02 '19 at 16:57

ElChiniNet

2,778
2
19
27

answered May 16 '13 at 10:10

Kshitij

8,474
2
26
34

@Kshitij - Why to use subquery when we can do [it directly](http://stackoverflow.com/a/16584882/1369235)? – Himanshu May 16 '13 at 10:28
2

hims056' solution is the better one - avoid subquerys if you're able to – fubo Nov 11 '14 at 16:01
11

Add alias after closing parenthesis. – Gene Kelly Oct 22 '15 at 18:42
2

The reason for this answer is that it will work when you need a having clause. The [answer below](http://stackoverflow.com/a/16584882/1369235) relies on being able to rewrite the query without a having clause. – Marlin Pierce Jul 03 '19 at 15:23
@MarlinPierce: Just to be curious, may I know an example of the scenario you have mentioned? – Himanshu Jul 17 '23 at 06:47
The case is if instead of "WHERE labref = 'NDQA201303001'" you wanted all labref values but filtering for those with count = 3. With Kshitij's answer you can remove the WHERE clause from the sub-query, then the outer query can GROUP BY labref, and a HAVING clause can filter for the values with count = 3. – Marlin Pierce Jul 19 '23 at 19:51

score 11 · Answer 3 · answered Dec 05 '20 at 16:57

11

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.

answered Dec 05 '20 at 16:57

nojitsi

351
3
13

2

This is the correct way of doing this. Works even when using `limit x`. – John C Apr 30 '21 at 16:22
1

Note that you need to be on MySQL 8+ to use `OVER()` (and other window functions). – Collin Krawll Feb 11 '22 at 04:46
It didn't worked. – menoktaokan Jul 05 '23 at 11:29
@menoktaokan check your sql version and your query – nojitsi Jul 06 '23 at 12:06

score 0 · Answer 4 · answered Oct 02 '18 at 19:14

0

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

answered Oct 02 '18 at 19:14

Mohd Abdul Mujib

13,071
8
64
88

1

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`. – ToolmakerSteve Jan 20 '19 at 02:51
It should also be noted that bringing in a large table into PHP may hit an overload memory limit for PHP and "Fatal Error" the code. Easier on memory to allow MySQL to count the rows. – Dawson Irvine Dec 05 '20 at 19:41
Because that gives you total rows returned from table, not total rows in table matching the query. Try adding a `limit 10` of a query that has 1000. – John C Apr 30 '21 at 16:21

score -1 · Answer 5 · answered Apr 08 '23 at 08:52

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.

score -2 · Answer 6 · answered Jul 26 '20 at 00:02

-2

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

answered Jul 26 '20 at 00:02

user3400085

1
1

Counting number of grouped rows in mysql

6 Answers6

See this SQLFiddle

Linked