Join two tables, sum, and separate by week of the month

Question

I have two tables, budget, and transaction like below:

Budget

|id | acc_code |   desc  |
|:-:|----------|---------|
|1  |A.IV.1    |Car      |
|2  |A.X.3     |Bike     |
|3  |B.I.1     |Airplane |

Transaction

|id | acc_code | price | transac_date |
|:-:|----------|-------|--------------|
|1  |A.IV.1    |200    | 2021-07-01 |
|2  |A.IV.1    |300    | 2021-07-02 |
|3  |A.X.3     |50     | 2021-07-06 |
|4  |A.IV.1    |250    | 2021-07-06 |
|5  |A.X.3     |75     | 2021-07-12 |
|6  |B.I.1     |4500   | 2021-07-12 |

I need to get query result table like below:

|id | acc_code |   desc  | week 1 | week2 | week 3 | week 4 | week 5 | total of month |
|:-:|----------|---------|--------|-------|--------|--------|--------|-------|
|1  |A.IV.1    |Car      |500     |       |        |        |        | 500 |
|2  |A.X.3     |Bike     |        | 50    |  75    |        |        | 125 |
|3  |B.I.1     |Airplane |        |       | 4500   |        |        | 4500 |

What I have done so far is as follows:

// Create connection
<?
  $connect = mysqli_connect($servername, $username, $password, $dbname);
  // Check connection
  if (!$connect) {
   die("Connection failed: " . mysqli_connect_error());
  }

  $sql = "
SELECT
  budget.acc_code,
  budget.desc AS description,
  COALESCE(SUM(transaction.price), 0) AS total
FROM
  budget
LEFT JOIN
  transaction
    ON budget.acc_code = transaction.acc_code
GROUP BY
  budget.desc
";

$query = mysqli_query($connect,$sql) or die (mysqli_error());

?>
<table>
    <thead style="background-color: #33DFFF;" >
        <tr class="info">
          <th>Num</th>
          <th>Acc_code</th>
          <th>Desc</th>
          <th>Total of month</th>            
        </tr>
    </thead>
    <tbody>
        <?php
            $check = mysqli_num_rows($query);
            if($check > 1)
            {
                $num=1;
                while($data = mysqli_fetch_array($query))
                  {                                             
        ?>
                      <tr>
                        <td><?php echo $num; ?></td>
                        <td><?php echo $data['acc_code']; ?></td>
                        <td><?php echo $data['description']; ?></td>
                        <td><?php echo $data['total']; ?></td>
                      </tr> 
        <?php                       
                      $num++;
                  }
             }
        ?>
    </tbody>
</table>
<?
mysqli_close($connect);
?>

with the following results:

|num | acc_code |   desc  | total of month |
|:-:|----------|---------|----------------|
|1  |A.IV.1    |Car      |500 |
|2  |A.X.3     |Bike     |125 |
|3  |B.I.1     |Airplane |4500 |

The problem is how to parse the data into weekly and add weekly columns. My experience in doing Join Tables is actually still lacking.

yes, but i can update it.. it says it was closed.. so i ask again with new update about what i've done so far — Jymo, Jul 02 '21 at 10:09
If your question is closed, you should update it and request that it be reopened. — Barmar, Jul 02 '21 at 10:09
See https://stackoverflow.com/questions/785206/function-for-week-of-the-month-in-mysql for how to get the week number of the month. — Barmar, Jul 02 '21 at 10:10
yes i got it.. "SELECT WEEK(transac_date, 5) - WEEK(DATE_SUB(transac_date, INTERVAL DAYOFMONTH(transac_date) - 1 DAY), 5) + 1 from transaction".. but i don't know how to compare it with first query then return week columns.. thanks for advice — Jymo, Jul 02 '21 at 10:19
You want to use that in a pivot. See https://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns — Barmar, Jul 02 '21 at 10:20
You have an error. [`mysqli_error()`](https://www.php.net/manual/en/mysqli.error.php) needs one argument. Please consider switching error mode on instead. [How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) — Dharman, Jul 02 '21 at 10:48
Start by generating a result set with one row per item and week, grouping by calendar week. Read this: https://stackoverflow.com/questions/1736010/how-to-group-by-week-in-mysql When you have that result set, [tag:pivot] it either in SQL or php to generate week-by-week columns. Solve one problem at a time—if you try to solve them both at once you'll get hopelessly snarled up in SQL. — O. Jones, Jul 02 '21 at 10:58

score 0 · Accepted Answer · answered Jul 02 '21 at 15:51

0

The next query can be used in this case:

select
    month(transac_date) mnth,
    acc_code,
    sum(if((FLOOR((DAYOFMONTH(transac_date) - 1) / 7) + 1 = 1), price, 0)) week1,
    sum(if((FLOOR((DAYOFMONTH(transac_date) - 1) / 7) + 1 = 2), price, 0)) week2,
    sum(if((FLOOR((DAYOFMONTH(transac_date) - 1) / 7) + 1 = 3), price, 0)) week3,
    sum(if((FLOOR((DAYOFMONTH(transac_date) - 1) / 7) + 1 = 4), price, 0)) week4,
    sum(if((FLOOR((DAYOFMONTH(transac_date) - 1) / 7) + 1 = 5), price, 0)) week5,
    sum(price) total_month
from transactions
group by month(transac_date), acc_code;

Test it here

answered Jul 02 '21 at 15:51

Slava Rozhnev

9,510
6
23
39

Yes.. thanks for helping me.. my next step is just to joining it.. cheers.. :) – Jymo Jul 03 '21 at 12:21
Look this: https://sqlize.online/?phpses=&sqlses=9b71a7874586a73f962c11a7de344910&php_version=null&sql_version=mysql80 – Slava Rozhnev Jul 04 '21 at 15:48

Join two tables, sum, and separate by week of the month

1 Answers1