CODEWITHSUNDEEP
mysqldatabase
Devator
Devator

Reputation: 3904

MySQL count duplicate row

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.

Upvotes: 1

Views: 1468

Answers (5)

atlau
atlau

Reputation: 971

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

Upvotes: 0

cwallenpoole
cwallenpoole

Reputation: 82018

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

Upvotes: 5

nobody
nobody

Reputation: 10645

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.

Upvotes: 2

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

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

Upvotes: 2

Jordan Wallwork
Jordan Wallwork

Reputation: 3114

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

Upvotes: 2

Related Questions