MySQL count duplicate row

Question

I have a table like this:

jobid, orderid

And with some data inside:

jobid, orderid
1245, 6767
1235, 9058
6783, 6767
4991, 6767
9512, 9058
5123, 1234

Now I want the following output:

jobid, orderid, orderid(total)
1245, 6767, 3
1235, 9058, 2
6783, 6767, 3
4991, 6767, 3
9512, 9058, 2
5123, 1234, 1

Now, the COUNT() doesn't work the way I want to, and I probably need some group by but I don't know how.

Thanks in advance.

cwallenpoole · Accepted Answer · 2011-08-15 15:00:07Z

5

It looks like you're trying to get rows which look like jobid, orderid, number of times that orderid appears. For that, you could use a subquery:

 SELECT jobid, orderid, 
        (SELECT COUNT(*) FROM 
         MY_TABLE INR 
             WHERE INR.orderid = OTR.orderid) as "orderid(total)"
 FROM MY_TABLE OTR

edited Aug 15, 2011 at 15:00

answered Aug 15, 2011 at 14:49

cwallenpoole

82.4k26 gold badges132 silver badges174 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

dtbarne Over a year ago

This is a lot more work for the SQL engine than Tim's solution.

cwallenpoole Over a year ago

@dtbarne It looks like what the OP is asking for.

Devator Over a year ago

I think your SQL statement is incorrect, since when I replace MY_TABLE with my table, I have an error. Also INNER seems out of place (and outer) but when I remove them, I get a total count of all rows and not what I need.

cwallenpoole Over a year ago

@Devator I had intended to abbreviate inner and outer. They're SQL keywords, so that might explain the breaking.

ypercubeᵀᴹ Over a year ago

This is correct (although I wouldn't use INNER or OUTER as aliases.)

|

nobody · Accepted Answer · 2011-08-15 14:59:59Z

2

Why are doing it this way? You will be doing a lot of redundant countings and put a lot of unnecessary pressure on your server. I would do this with two queries:

SELECT jobid, orderid FROM my_table

to get the complete list, and then:

SELECT orderid, COUNT(*) FROM my_table GROUP BY orderid

to get the total count for each orderid. Then combine these two results in your application. This will be much faster than your solution.

answered Aug 15, 2011 at 14:59

nobody

10.7k4 gold badges29 silver badges43 bronze badges

5 Comments

ypercubeᵀᴹ Over a year ago

Unnecessary pressure is not the word I'd use. The database will just be just sending a simple integer column (with few or many duplicates). In that view, your approach puts "unnecessary pressure" on the application to combine the results (some sorting or hashing will be needed).

nobody Over a year ago

@ypercube I don't know what language is OP using but in PHP I would fill a $count array with $orderid => $count key/value pairs from the second query and when iterating through the results from the first query I would get the count for each row from that array $count[ $orderid ] Adds very little pressure on the application side.

ypercubeᵀᴹ Over a year ago

I agree. The same does the other approach, little pressure on the db. Only some redundance in the transfer of results.

nobody Over a year ago

Well this is an alternative approach. Which one performs better depends on many factors including the distribution of rows with simillar orderid in that table. OP can try both. I personally usually first go with the approach that has less redundancies.

Devator Over a year ago

Thank you. I'm going with this way since my this will go a lot faster. I have thought of this before but I thought it would be better using one query, but it's not the case definately.

Jordan Wallwork · Accepted Answer · 2011-08-15 14:51:14Z

2

SELECT jobid, orderid, count(orderid)
FROM sometable
GROUP BY orderid, jobid

answered Aug 15, 2011 at 14:51

Jordan Wallwork

3,1122 gold badges26 silver badges51 bronze badges

2 Comments

Devator Over a year ago

Doesn't work either, since count(orderid) always gives me "1".

ypercubeᵀᴹ Over a year ago

This query is not correct. It won't count all occurences of same orderid.

ypercubeᵀᴹ · Accepted Answer · 2011-08-15 14:59:16Z

2

SELECT t.jobid
     , t.orderid
     , grp.orderid_total
FROM
    tableX AS t
  JOIN
    ( SELECT orderid
           , COUNT(*) AS orderid_total
      FROM tableX
      GROUP BY orderid
    ) AS grp
      ON grp.orderid = t.orderid

answered Aug 15, 2011 at 14:59

ypercubeᵀᴹ

116k19 gold badges181 silver badges248 bronze badges

Comments

Devin Burke · Accepted Answer · 2012-01-09 22:40:08Z

0

select jobid, orderid, count(*) from table group by orderid;

edited Jan 9, 2012 at 22:40

Devin Burke

13.8k12 gold badges60 silver badges85 bronze badges

answered Aug 15, 2011 at 14:52

atlau

1,0231 gold badge9 silver badges17 bronze badges

Collectives™ on Stack Overflow

MySQL count duplicate row

5 Answers 5

6 Comments

5 Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

6 Comments

5 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related