CODEWITHSUNDEEP
phpmysql
Masoud Tavakkoli
Masoud Tavakkoli

Reputation: 1020

how to count a user coupon in sql - mysql

Hi I'm newbie in MySQL

I want to count a user coupon token that used by other people to use in app

I currently use a php loop to calculate how many people use a user token

Is there any way to do this in one query that return all users and its used coupon number? I want something like this SELECT user.id , Count(payment.id) from ..

please pay attention I don't use user.id = ?

I loop this function to achieve every user coupon used number that is too slow

   public function countInvitationByUserID($id) {
        $query = $this->db->query("
            SELECT * FROM payment, coupon, users
            WHERE payment.payment_coupon_id = coupon.id
            AND coupon.coupon_token = users.user_coupon_token
            AND users.id = ?
            AND payment.payment_status = 1", array($id)
        );
        return $query->num_rows();
    }

anyone could help to write all of this in one query?

Thanks

Upvotes: 0

Views: 377

Answers (1)

Sagar Gangwal
Sagar Gangwal

Reputation: 7937

    SELECT 
    US.user_id, COUNT(CO.id)
FROM
    payment PT
    INNER JOIN coupon CO ON PT.payment_coupon_id=CO.id
    INNER JOIN users US ON US.user_coupon_token=CO.coupon_token

WHERE
    PT.payment_status = 1
GROUP BY US.user_id;

Try above query.

Hope this will help you.

Upvotes: 1

Related Questions