mysql multi count and sum with WHERE

i'm trying to build a query to check my stock.

SELECT COUNT(*) AS item_count,
reseller_id, sum(sold) as
sold_count, sum(refunded) as
refunded_count,**sum(case price开发者_JS百科 when
refunded <> 1 AND sold=1) as pricesum** FROM stock
GROUP BY
reseller_id ORDER BY sold_count ASC

The above query will select all items and group them by each reseller with total items and refund count. The part in bold is wrong i want to get the total price(which is the sum for the none refunded + sold for each of the resellers (notice i group by reseller id)

Something like this with the case statements might work:

SUM(CASE
    WHEN refunded <> 1 AND sold = 1
        THEN price
    ELSE
        0
    END)

Things that I noticed:

• You haven't ended your case. This is done by just writing "end"
• You haven't given a else case

Try to make a subquery like:

SELECT SUM(price) FROM stock WHERE refunded != 1 AND sold = 1

The complete query would look like this:

SELECT
  COUNT(*) AS item_count,
  reseller_id,
  SUM(sold) as sold_count,
  SUM(refunded) as refunded_count,
  (SELECT SUM(price) FROM stock WHERE refunded != 1 AND sold = 1 WHERE reseller_id=1 GROUP BY reseller_id) as pricesum
FROM stock
WHERE reseller_id=1
GROUP BY reseller_id
ORDER BY sold_count ASC