CODEWITHSUNDEEP
mysqlsqlmariadb
xuweisen
xuweisen

Reputation: 113

Select count paid order by user in the same table

If example I have this table

-----------------
|ID|user_no|paid|
|---------------|
|1 |1      |yes |
|2 |1      |yes |
|3 |1      |no  |
|4 |2      |yes |
|5 |2      |no  |
|---------------|

I want the result to be

-----------------------------
|ID|user_no|order_paid_count|
|---------------------------|
|1 |1      |2               |
|2 |1      |2               |
|3 |1      |2               |
|4 |2      |1               |
|5 |2      |1               |
|---------------------------|

I do the query like this, but it doesnt work.. it loading so long and didnt come out result, its like loop infinity

SELECT o1.*, (SELECT COUNT(*) FROM order AS o2 WHERE o1.user_no=o2.user_no AND o2.paid=yes) as order_paid_count 
FROM order as o1

Upvotes: 0

Views: 51

Answers (2)

Mansoor
Mansoor

Reputation: 4192

Use DENSE_RANK method to achieve your result :

 SELECT ID , user_no , DENSE_RANK() OVER (ORDER BY user_no DESC) 
        order_paid_count 
 FROM #Table 
 ORDER BY user_no

Upvotes: 0

jarlh
jarlh

Reputation: 44766

Have a sub-query that counts each user_no's number of paid orders. LEFT JOIN with that result:

select o1.id, o1.user_no, o2.order_paid_count
from order as o1
left join (select user_no, count(*) as order_paid_count
           from order
           where paid = 'yes'
           group by user_no) as o2
  on o1.user_no = o2.user_no

(LEFT JOIN to include also user_no's with no paid orders at all.)

Upvotes: 1

Related Questions