Count transactions AND sum quantity on specific transactions by type

Options

I have a card that counts the transactions type by month. Some of the transactions have a quantity so I need to sum the quantity instead of just counting that transaction one time. I'm not quite sure how to go about that. I need to be able to say if it is this type of transaction then sum the quantity otherwise count the transaction. My attempts at a best mode have not worked out.

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓
    Options

    Are your transactions duplicated?

    If not you could do a conditional sum like:

    SUM(CASE WHEN `transaction_type` = 'Quantity' THEN `quantity` ELSE 1 END)
    

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓
    Options

    Are your transactions duplicated?

    If not you could do a conditional sum like:

    SUM(CASE WHEN `transaction_type` = 'Quantity' THEN `quantity` ELSE 1 END)
    

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Sean_Tully
    Sean_Tully Contributor
    Options

    Agree with @GrantSmith.

    Another calc that would count the transaction as 1 if there is no quantity would be sum(ifnull(`quantity`, 1)).

  • user18872
    user18872 Member
    Options

    Thank you! This worked perfectly! I tend to overthink things.