CODEWITHSUNDEEP
phpmysql
user18066144
user18066144

Reputation:

MySQL PHP Dynamic table

I have a MySQL Data table and I want to get out the datas sum by month and sum by wood type. I get the total sum but I dont get for wood types.

Hear is my MySQL table:

furesz:

wood cubic date machine
A-001 0,25 2022-02-01 18:13:12 mebor1
A-003 0,35 2022-02-01 18:13:12 mebor1

My code:

        <div class="col-sm">
            <table class="table">
                <thead>
                    <tr>
                    <th scope="col">Hónap</th>
                    <th scope="col">Nyár</th>
                    <th scope="col">Tölgy</th>
                    <th scope="col">VTölgy</th>
                    <th scope="col">Cser</th>
                    <th scope="col">SUM</th>
                    </tr>
                </thead>
                <tbody>
                <?php
                    $sql_list = 
                    "SELECT MONTH(date) AS month,   
                    COUNT(DISTINCT DATE(date)) AS work_days, 
                    SUM(IF(cubic='A-001',1,0)) sum_nyar,
                    SUM(IF(cubic='A-003',1,0)) sum_tölgy,
                    SUM(IF(cubic='A-004',1,0)) sum_vtölgy,
                    SUM(IF(cubic='A-018',1,0)) AS sum_cser,
                    SUM(cubic) AS sum_full
                    FROM furesz 
                    WHERE machine='mebor2'
                    GROUP BY month";
                    $result_list = mysqli_query($conn, $sql_list);
                    while($row = mysqli_fetch_assoc($result_list)) {  
                        ?>
                    <tr>
                    <th scope="row"><?=$row['month']?></th>
                    <td><?=number_format ($row['sum_nyar'],1)?>&nbsp;m3</td>
                    <td><?=number_format ($row['sum_tölgy'],1)?>&nbsp;m3</td>
                    <td><?=number_format ($row['sum_vtölgy'],1)?>&nbsp;m3</td>
                    <td><?=number_format ($row['sum_cser'],1)?>&nbsp;m3</td>
                    <th><?=number_format ($row['sum_full'],1)?>&nbsp;m3</th>
                    <?php
                }
                ?>       
                </tbody>
            </table>
        </div>

This is the output I get:

month nyár tölgy VTölgy Cser SUM
2 0.0 m3 0.0 m3 0.0 m3 0.0 m3 0.6 m3

This is what I'am looking for:

Month Nyár Tölgy VTölgy Cser SUM
2 0.25 m3 0.35 m3 0.0 m3 0.0 m3 0.6 m3

Upvotes: 0

Views: 43

Answers (1)

Niek
Niek

Reputation: 26

The following code would work. You mixed up the wood and cubic column. So you want to sum the cubic if wood equals a string.

SELECT 
    MONTH(date) AS month,
    COUNT(DISTINCT DATE(date)) AS work_days,
    SUM(IF(wood='A-001',cubic,0)) sum_nyar,
    SUM(IF(wood='A-003',cubic,0)) sum_tölgy,
    SUM(IF(wood='A-004',cubic,0)) sum_vtölgy,
    SUM(IF(wood='A-018',cubic,0)) AS sum_cser,
    SUM(cubic) AS sum_full
  FROM furesz 
  WHERE machine='mebor2'
  GROUP BY month

Upvotes: 1

Related Questions