Add table definitions.
[xonotic/xonstatdb.git] / scripts / update_summary_stats.sql
1 begin transaction;
2     -- get rid of the existing summary stats since we're about to refresh
3     delete from summary_stats;
4
5     insert into summary_stats(total_players, total_servers, total_games,
6                               total_dm_games, total_duel_games, total_ctf_games)
7     with total_games as (
8        select game_type_cd, count(*) total_games
9        from games
10        where game_type_cd in ('duel', 'dm', 'ctf')
11        group by game_type_cd
12     ),
13     total_players as (
14        select count(*) total_players
15        from players
16        where active_ind = true
17     ),
18     total_servers as (
19     select count(*) total_servers
20        from servers
21        where active_ind = true
22     )
23     select tp.total_players, ts.total_servers, dm.total_games+duel.total_games+ctf.total_games total_games,
24            dm.total_games dm_games, duel.total_games duel_games, ctf.total_games ctf_games
25     from   total_games dm, total_games duel, total_games ctf, total_players tp, total_servers ts
26     where  dm.game_type_cd = 'dm'
27     and    ctf.game_type_cd = 'ctf'
28     and    duel.game_type_cd = 'duel';
29
30 end;