CODEWITHSUNDEEP
mysqlsql
Dimo
Dimo

Reputation: 3280

SQL Query for returning rows divided by date

I have a query like this:

SELECT *, 
SUM(money_deposit + bonus_deposit) as money_deposit_total,
SUM(money_withdraw + bonus_withdraw) as money_withdraw_total
FROM transactions 
where player_id = 1 and created_date between '2013-01-01' and '2014-01-05' 
group by game_id;

What I want to do is to return the results by date, so instead of returning only 1 row, I want to return 1 row for each date.

Any suggestions?

Upvotes: 1

Views: 61

Answers (4)

borjab
borjab

Reputation: 11655

It is easy if you remove the *. You can't show a transaction field that is not used in the group by without an aggregation function. One common trick is to use the MAX function if you know all the values are the same for a given date and game_id.

SELECT created_date
       ,game_id
       ,SUM(money_deposit + bonus_deposit) as money_deposit_total
       ,SUM(money_withdraw + bonus_withdraw) as money_withdraw_total
       ,Max( field_1) as field1
       ,Max( field_2) as field1
FROM transactions 
where player_id = 1 and created_date between '2013-01-01' and '2014-01-05' 
group by game_id,
         created_date

Another alternative is to use subqueries in the select 

SELECT created_date
       ,( Select game_name from game g where g.game_id = t.game_id) as name
       ,SUM(money_deposit + bonus_deposit) as money_deposit_total
       ,SUM(money_withdraw + bonus_withdraw) as money_withdraw_total

[...] or join afterwards

   SELECT *
      FROM ( SELECT created_date
           ,game_id
           ,SUM(money_deposit + bonus_deposit) as money_deposit_total
           ,SUM(money_withdraw + bonus_withdraw) as money_withdraw_total
           ,Max( field_1) as field1
           ,Max( field_2) as field1
    FROM transactions 
    where player_id = 1 and created_date between '2013-01-01' and '2014-01-05' 
    group by game_id,
             created_date ) AUX
  JOIN game g ON g.gami_id = AUX.game_id

Upvotes: 0

Andolasoft Inc
Andolasoft Inc

Reputation: 1296

Please use the group by function for crated date column.

SELECT *, 
SUM(money_deposit + bonus_deposit) as money_deposit_total,
SUM(money_withdraw + bonus_withdraw) as money_withdraw_total
FROM transactions 
where player_id = 1 and created_date between '2013-01-01' and '2014-01-05' 
group by game_id,created_date;

Upvotes: 1

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

Try this

SELECT * FROM
   (
      SELECT game_id,created_date,
      SUM(money_deposit + bonus_deposit) as money_deposit_total,
      SUM(money_withdraw + bonus_withdraw) as money_withdraw_total
      FROM transactions 
      where player_id = 1 and created_date between '2013-01-01' and '2014-01-05' 
      group by game_id,game_DATE;
    ) AS T INNER JOIN transactions S ON S.game_id = T.game_id

Upvotes: 1

ASNAOUI Ayoub
ASNAOUI Ayoub

Reputation: 472

You simply add this 

group by game_DATE;

At the end of your sql query

Upvotes: 3

Related Questions