Viki Cullen Viki Cullen - 1 year ago 60
MySQL Question

Select count in mysql using group by and repeat the count in each row

I have my table called 'bills' something like below

Bill item totalprice

BILL_1 Fossil Watch 9000
BILL_1 Fastrack -
BILL_1 Fastrack -
BILL_2 Woodlands 7000
BILL_2 Woodlands -
BILL_3 Denim Shirt 9000
BILL_3 Levis Jean -

Now i need the result like the below structure

Bill item totalprice bill_count

BILL_1 Fossil Watch 9000 3
BILL_1 Fastrack - 3
BILL_1 Fastrack - 3
BILL_2 Woodlands 7000 2
BILL_2 Woodlands - 2
BILL_3 Denim Shirt 9000 2
BILL_3 Levis Jean - 2

I tried with the below query,

SELECT bill,item,totalPrice,COUNT(bill) FROM bills GROUP BY bill;

but i didn't get the result as expected.

Answer Source
SELECT b.bill, b.item, b.totalPrice, tmp.cnt
FROM bills b
   select bill, COUNT(bill) as cnt
   from bills
   group by bill
) tmp on tmp.bill = b.bill
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download