CODEWITHSUNDEEP
mysqlsql
孙欣乐
孙欣乐

Reputation: 418

MySQL Get COUNT and SUM in a Single Query

I use this query:

SELECT 
      `o_p`.`id`, 
      `o`.`user_id` AS `buyer`,
      `product_number` 
FROM `order_products` `o_p` 
LEFT JOIN `order` `o` ON o.id=o_p.id 
WHERE (`o_p`.`product_id`='5') AND (`o`.`pay_status`=1)

then i get this result:

id buyer product_number

20 7     13

26 7     10

27 19    10

or you can see the result at this link

But I want to count the buyer's number which is expected '2',and the total of product_number which is expected '33'.

How should I fix the query.

Upvotes: 1

Views: 83

Answers (2)

Rigel1121
Rigel1121

Reputation: 2016

Use COUNT(o.user_id) for the number of unique buyers and SUM(product_number) for the total of product_number. Lastly, GROUP BY o.user_id.

SELECT
      COUNT(o.user_id) AS buyer_number,
      SUM(product_number) AS product_number_total
FROM order_products o_p 
LEFT JOIN order o ON o.id=o_p.id 
WHERE (o_p.product_id='5') AND (o.pay_status=1)
GROUP BY o.user_id

Upvotes: 1

FuzzyTree
FuzzyTree

Reputation: 32392

Use count(distinct user_id) for the # of unique buyers and sum(product_number) for the total of product_number

select count(distinct o.user_id) as buyer_number,
sum(product_number) as product_number_total
from order_products o_p 
left join order o on o.id=o_p.id 
where (o_p.product_id='5') and (o.pay_status=1)

Upvotes: 1

Related Questions