MySQL Get COUNT and SUM in a Single Query

Question

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.

FuzzyTree · Accepted Answer · 2015-03-10 02:31:51Z

1

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)

answered Mar 10, 2015 at 2:31

FuzzyTree

32.4k3 gold badges58 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

FuzzyTree Over a year ago

@Rigel1121 It seems op wants the sum and count for the entire table and not per id because the example contains multiple ids yet the desired result are the counts and sums of the entire table

Rigel1121 · Accepted Answer · 2015-03-10 02:54:43Z

1

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

edited Mar 10, 2015 at 2:54

answered Mar 10, 2015 at 2:49

Rigel1121

2,0171 gold badge19 silver badges25 bronze badges

Collectives™ on Stack Overflow

MySQL Get COUNT and SUM in a Single Query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related