CODEWITHSUNDEEP
mysql
Scorpioniz
Scorpioniz

Reputation: 1449

Mysql get all data from table and count of some columns

have table table 1 structure like:

`id, rma_id, order_id, product_id, order_nr, comment, admin_comment, add_date`

and data of it:

(40, 1, 110331, 4399, 1, 'comment1', '', '2016-09-30 08:46:54'),
(42, 2, 110331, 4399, 1, 'comment2', '', '2016-09-30 11:18:06'),
(43, 3, 110374, 4399, 1, 'comment3', '', '2016-10-03 05:55:25'),
(44, 4, 110374, 4399, 1, 'comment4', '', '2016-10-03 05:55:43'),
(45, 4, 110374, 4399, 2, 'comment5', '', '2016-10-03 05:55:43');

Using query like this to get all that of exact RMA:

SELECT * FROM `rma_products` WHERE `rma_id` = 4

and getting result like this:

[0] => array(8) {
    ["id"] => string(2) "44"
    ["rma_id"] => string(1) "4"
    ["order_id"] => string(6) "110374"
    ["product_id"] => string(4) "4399"
    ["order_nr"] => string(1) "1"
    ["comment"] => string(16) "comment4"
    ["admin_comment"] => string(0) ""
    ["add_date"] => string(19) "2016-10-03 08:55:43"
}
[1] => array(8) {
    ["id"] => string(2) "45"
    ["rma_id"] => string(1) "4"
    ["order_id"] => string(6) "110374"
    ["product_id"] => string(4) "4399"
    ["order_nr"] => string(1) "2"
    ["comment"] => string(7) "comment5"
    ["admin_comment"] => string(0) ""
    ["add_date"] => string(19) "2016-10-03 08:55:43"
}

but I want to add one more field like count that my result would be like this: 

[0] => array(8) {
    ["id"] => string(2) "44"
    ["rma_id"] => string(1) "4"
    ["order_id"] => string(6) "110374"
    ["product_id"] => string(4) "4399"
    ["order_nr"] => string(1) "1"
    ["comment"] => string(16) "comment4"
    ["admin_comment"] => string(0) ""
    ["add_date"] => string(19) "2016-10-03 08:55:43"
    ["count"] => string(1) "2"
}
[1] => array(8) {
    ["id"] => string(2) "45"
    ["rma_id"] => string(1) "4"
    ["order_id"] => string(6) "110374"
    ["product_id"] => string(4) "4399"
    ["order_nr"] => string(1) "2"
    ["comment"] => string(7) "comment5"
    ["admin_comment"] => string(0) ""
    ["add_date"] => string(19) "2016-10-03 08:55:43"
    ["count"] => string(1) "2"
}

which would be counted by how many same product_id is in same rma_id

Upvotes: 0

Views: 30

Answers (1)

P.Salmon
P.Salmon

Reputation: 17640

select t.*,s.cnt from t
join
(select rma_id,product_id,count(*) cnt from t where rma_id = 4 group by rma_id,product_id) s 
on s.rma_id = t.rma_id and t.product_id = s.product_id

Upvotes: 1

Related Questions