Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2013
    Posts
    13

    Question Unanswered: Help joining two grouped selects please

    Hello everybody, i am trying to get a results set from two tables but i am needing some help
    The two tables are as follows:

    club_operations
    |-----date-----|-----amount----|
    |---------------|-----------------|

    club_logs
    |-----date-----|-----bet-----|-----win-----|
    |---------------|-------------|--------------|

    I am trying to get the following:

    |----date(date)-----|--bets_sum_for_day--|--win_sum_for_day--|--operations_sum_for_day--|
    |---------------------|-------------------------|-----------------------|--------------------------------|

    If i make the query

    Code:
    SELECT DATE(logs.date) AS 'date', SUM(logs.win) AS 'win', SUM(logs.bet) AS 'bet' FROM club_logs AS logs
    GROUP BY DATE(date)
    i get

    |----date(date)-----|--bets_sum_for_day--|--win_sum_for_day--|
    |----2014-10-07----|-------------------------|-----------------------|
    |----2014-10-08----|-------------------------|-----------------------|
    |----2014-10-09----|-------------------------|-----------------------|

    and if i make the query

    Code:
    SELECT DATE(ops.date) AS  `date`, SUM(ops.amount) AS `sum` FROM club_operations AS ops
    GROUP BY date(ops.date)
    i get
    |----date(date)-----|--operations_sum_for_day--|
    |----2014-10-07----|-------------------------------|
    |----2014-10-08----|-------------------------------|
    |----2014-10-09----|-------------------------------|

    But i am having trouble joining the two to get a list of all the sum of bets, wins, and operations for each day as follows

    |----date(date)-----|--bets_sum_for_day--|--win_sum_for_day--|--operations_sum_for_day--|
    |---------------------|-------------------------|-----------------------|--------------------------------|

    I need to join both SELECTs on date(date)
    Any help would be very much appreciated!
    Last edited by gastongr; 10-09-14 at 01:18.

  2. #2
    Join Date
    Jan 2013
    Posts
    13
    Got it, just in case someone needs this, here is a way to join two grouped by selects:

    Code:
    SELECT *
    FROM (SELECT a, b, c FROM table1
    GROUP BY a) AS S1
    INNER JOIN (a, b, c FROM table2
    GROUP BY a) AS S2
    ON S1.a=S2.a

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •