CODEWITHSUNDEEP
mysqlsum
user3369825
user3369825

Reputation: 99

mysql SUM column on WHERE condition

Im trying to get a total sum of all the columns that meet a condition. Here is my current setup

    SELECT 
                      COUNT(order_type = 'BUY') AS buy_fill,
                      COUNT(order_type = 'SELL') AS sell_fill,
                      SUM(btc_total) AS fill_sum
                      FROM fill_orders
                      WHERE coin_id = '$coin'
                      AND time_stamp >= DATE_SUB(NOW(), INTERVAL 55 SECOND)

This is what I have now and its counting how many types of buy and sell orders I have and is also giving me sum of all orders but I need to the break the sum of the orders into BUY orders and SELL orders.

Heres the code that I'm trying trying to make work. I've added (btc_total WHERE order_type = 'BUY') and SUM(btc_total WHERE order_type = 'SELL')

      SELECT 
                            COUNT(order_type = 'BUY') AS buy_fill,
                            COUNT(order_type = 'SELL') AS sell_fill,
                            SUM(btc_total) AS fill_sum,

                            SUM(btc_total WHERE order_type = 'BUY') AS buy_total
                            SUM(btc_total WHERE order_type = 'SELL') AS sell_total
                      FROM fill_orders
                      WHERE coin_id = '$coin'
                      AND time_stamp >= DATE_SUB(NOW(), INTERVAL 55 SECOND)

Upvotes: 0

Views: 4938

Answers (2)

spencer7593
spencer7593

Reputation: 108400

Change this:

SUM(btc_total WHERE order_type = 'BUY') AS buy_total

to this:

SUM(IF(order_type='BUY',btc_total,NULL)) AS buy_total

The MySQL IF() function evaluates the first argument as a boolean, if that's TRUE, it returns the second argument, else it returns the third argument.

The IF() will be evaluated for each row, and the return from that expression will get totaled up by the SUM() aggregate.

or, use the more ANSI-standard equivalent to achieve the same result:

SUM(CASE WHEN order_type = 'BUY' THEN btc_total END) AS buy_total

This pattern is commonly referred to as "conditional aggregation".

For the "counts" we can replace COUNT with SUM, like this: 

SUM(order_type = 'BUY') AS buy_fill

MySQL evaluates the equality comparison as a boolean, which returns 1, 0 or NULL, which are then totaled up by the SUM aggregate. (A COUNT of that would include zeros and ones, not just the ones.)

The above is equivalent to

SUM( CASE
     WHEN order_type  = 'BUY' THEN 1
     WHEN order_type <> 'BUY' THEN 0
     ELSE NULL
     END
   ) AS buy_fill

If we want to use a COUNT aggregate, we could do it like this:

COUNT(IF(order_type = 'Buy',1,NULL)) AS buy_fill

(We could use any non-null value in place of 1, and get an equivalent result.)

Upvotes: 3

Paul Maxwell
Paul Maxwell

Reputation: 35583

"conditional aggregates" conventionally contain a case expression

SELECT
      COUNT(CASE WHEN order_type = 'BUY' THEN order_type END)      AS buy_fill
    , COUNT(CASE WHEN order_type = 'SELL' THEN order_type END)     AS sell_fill
    , SUM(btc_total)                                               AS fill_sum
    , SUM(CASE WHEN order_type = 'BUY' THEN btc_total ELSE 0 END)  AS buy_total
    , SUM(CASE WHEN order_type = 'SELL' THEN btc_total ELSE 0 END) AS sell_total
FROM fill_orders
WHERE coin_id = '$coin'
AND time_stamp >= DATE_SUB(NOW(), INTERVAL 55 SECOND)

Upvotes: 1

Related Questions