Good time of day
Is it possible to combine the following queries into one query ?:

select SUM(PROFIT) as PROFIT from ARCHIVE where PROFIT > 0.0 and DATE_END_RATE between '13.01.2017' and '14.01.2017'

With request

 select SUM(PROFIT) as LOSS from ARCHIVE where PROFIT < 0.0 and DATE_END_RATE between '13.01.2017' and '14.01.2017'

And

 select * from ARCHIVE where DATE_END_RATE between '13.01.2017' and '14.01.2017'

to immediately display all the entries in the ARCHIVE table, and also add two fields "PROFIT" and "LOSS" to it, in which to display the corresponding positive and negative amounts?

Thank you in advance

    2 answers 2

    Combining the first two is easy:

     select SUM(CASE WHEN PROFIT > 0.0 THEN PROFIT END) as PROFIT , SUM(CASE WHEN PROFIT < 0.0 THEN PROFIT END) as LOSS from ARCHIVE where DATE_END_RATE between '13.01.2017' and '14.01.2017'

    Regarding the third - these will be different levels of grouping, and combining them in one query does not make sense.

    • those. All the same, have to pull the server several times? - Konstantin78
    • Do you want to get both a list of transactions and totals with pluses / minuses? But these samples even differ in the number of fields - well, how to collect them into one data set? The only thing you can (if the connector allows) is to send two requests in one packet and receive two data sets in response. - Akina
    • I thought you could somehow sample the entire talits (query number 3), add two fields "profit" and "loss" and in each of their lines display both "plus" and "minus". But if it is impossible this way is impossible ... Do not view it in reality ... - Konstantin78
    • Why not? you can ... here is just an overhead of additional data in EVERY recording will devour all your ghostly gain from getting rid of the second request ... - Akina

    There are no window functions that allow you to immediately get the sum of several lines in MySQL on each line, so you have to accumulate the results in variables and output them in an external query.

     select *, @p as PROFIT, @l as LOST from ( select *, @p:=@p+if(PROFIT>0,PROFIT,0), @l:=@l+if(PROFIT<0,PROFIT,0) from ARCHIVE, (select @p:=0,@l:=0) A where DATE_END_RATE between '13.01.2017' and '14.01.2017' ) X