I've got a table such as this:
With some data inside:
jobid, orderid 1245, 6767 1235, 9058 6783, 6767 4991, 6767 9512, 9058 5123, 1234
Now I would like the next 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() does not work generate an income wish to, and that i most likely take some group by but I'm not sure how.
Thanks ahead of time.
It appears like you are looking to get rows which seem like
jobid, orderid, number of times that orderid appears. For your, you could utilize a subquery:
SELECT jobid, orderid, (SELECT COUNT(*) FROM MY_TABLE INR WHERE INR.orderid = OTR.orderid) as "orderid(total)" FROM MY_TABLE OTR
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
How come doing the work by doing this? You'll be doing lots of redundant countings and set lots of unnecessary pressure in your server. I'd do that with two queries:
SELECT jobid, orderid FROM my_table
to obtain the complete list, after which:
SELECT orderid, COUNT(*) FROM my_table GROUP BY orderid
to obtain the total count for every orderid. Then mix both of these leads to the application. This is considerably faster than your solution.
SELECT jobid, orderid, count(orderid) FROM sometable GROUP BY orderid, jobid
select jobid, orderid, count(*) from table group by orderid;