CODEWITHSUNDEEP
mysqlsum
user1708816
user1708816

Reputation: 11

mysql: select and sum in one statement

I want to select data from multiple tables and I want to get the sum from one column in one statement. If I just use sum (column) I limit my results to one row, if I use group by, sum (column) doesnt seem to work. Or I am just too stupid.

There is probably a more elegant solution, too. (its the sum in line 6). 

SELECT 
    lb.date AS l_date, 
    gbb.id AS bid, 
    gbb.cash AS b_cash, 
    gpr.cash AS p_cash, 
    sum (gbb.cash) as t_cash,
    gbb.name
FROM 
    log_buildings lb, 
    game_building_basic gbb, 
    game_player_buildings gpb, 
    game_player_ress gpr
WHERE 
    lb.pid = :pid
    AND gpr.id = :pid
    AND gpb.pid = :pid
    AND lb.date >= gpr.date
    AND lb.bid = gbb.id
    AND gpb.buildings_id = gbb.id
    AND gpb.pid = :pid";

Upvotes: 1

Views: 200

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

First, you should learn JOIN syntax for your query:

SELECT lb.date AS l_date, gbb.id AS bid, gbb.cash AS b_cash, gpr.cash AS p_cash,
       sum (gbb.cash) as t_cash, gbb.name
FROM log_buildings lb join
     game_building_basic gbb
     on lb.bid = bgg.bid join
     game_player_buildings gpb
     on gpb.buildings_id = gbb.id join
     game_player_ress gpr
     on lb.date >= gpr.date
WHERE lb.pid = :pid AND gpr.id = :pid AND gpb.pid = :pid AND gpb.pid = :pid"

As written, this query will return one set of random data for all the fields, along with the total sum. You can get the total on a separate row, by using rollup:

SELECT lb.date AS l_date, gbb.id AS bid, gbb.cash AS b_cash, gpr.cash AS p_cash,
       sum (gbb.cash) as t_cash, gbb.name
FROM log_buildings lb join
     game_building_basic gbb
     on lb.bid = bgg.bid join
     game_player_buildings gpb
     on gpb.buildings_id = gbb.id join
     game_player_ress gpr
     on lb.date >= gpr.date
WHERE lb.pid = :pid AND gpr.id = :pid AND gpb.pid = :pid AND gpb.pid = :pid"
group by lb.date, gbb.id, gbb.cash, gpr.cash, gbb.name with rollup

However, you want it as a separate column, not a separate row (and rollup produces multiple sums at different levels. For this, you need a subquery:

SELECT lb.date AS l_date, gbb.id AS bid, gbb.cash AS b_cash, gpr.cash AS p_cash,
       gbb.name,
       (select sum(gbb.cash)
        FROM log_buildings lb join
             game_building_basic gbb
             on lb.bid = bgg.bid join
             game_player_buildings gpb
             on gpb.buildings_id = gbb.id join
             game_player_ress gpr
             on lb.date >= gpr.date
        WHERE lb.pid = :pid AND gpr.id = :pid AND gpb.pid = :pid AND gpb.pid = :pid
       ) as t_cash
FROM log_buildings lb join
     game_building_basic gbb
     on lb.bid = bgg.bid join
     game_player_buildings gpb
     on gpb.buildings_id = gbb.id join
     game_player_ress gpr
     on lb.date >= gpr.date
WHERE lb.pid = :pid AND gpr.id = :pid AND gpb.pid = :pid AND gpb.pid = :pid

In almost any other database, you could do this with windows functions:

SELECT lb.date AS l_date, gbb.id AS bid, gbb.cash AS b_cash, gpr.cash AS p_cash,
       gbb.name,sum(gbb.cash) over ()

But MySQL does not support that syntax.

Upvotes: 1

Related Questions