Razvan2k3 Razvan2k3 - 2 months ago 6
MySQL Question

Select as total columns in a table based on the same ID

Hi,

I am working on a website (php and mysql) - it involves a management cpanel (database) of certain phone products.

I have some issues on a page, where I want to display some results:

My actual code is:

<table data-order='[[0, "desc"]]' id="datatable-buttons" class="table table-hover m-0 table-bordered">
<thead>
<tr>
<th>Entry ID</th>
<th>Producer</th>
<th>Model</th>
<th>Date</th>
<th>Total sum</th>
<th>Expenses</th>
<th>Profit</th>
</tr>
</thead>
<tbody>

<?php
$result = mysql_query("SELECT receptie.id
, receptie.marca_tel
, receptie.model
, receptie.data_primire
, articole_service.pret_sol
, articole_service.pret_achizitie
, articole_service.pret_sol - articole_service.pret_achizitie as profit
FROM receptie
inner join articole_service on receptie.id = articole_service.id_receptie
order by receptie.id desc");
while ($row = mysql_fetch_array($result))

{
?>
<tr>
<td><?php echo $row['id']; ?></td>
<td><?php echo $row['marca_tel']; ?></td>
<td><?php echo $row['model']; ?></td>
<td><?php echo $row['data_primire']; ?></td>
<td><?php echo $row['pret_sol']; ?></td>
<td><?php echo $row['pret_achizitie']; ?></td>
<td><?php echo $row['profit']; ?></td>
</tr>
<?php } ?>
</tbody>
</table>


Result as it is now:

Entry ID Producer Model Date Total sum Expenses Profit

**21** Apple Galaxy S4 2016-09-01 150 122 28
**21** Apple Galaxy S4 2016-09-01 145 15 130
**20** Apple iPhone 4s 2016-09-06 145 12 133
**20** Apple iPhone 4s 2016-09-06 180 150 30
**20** Apple iPhone 4s 2016-09-06 150 1 149


Desired result: (To display total of the columns based on the same Entry ID and not duplicate rows)

Entry ID Producer Model Date Total sum Expenses Profit

21 Apple Galaxy S4 2016-09-01 150+145 122+15 28+130
20 Apple iPhone 4s 2016-09-06 145+180+150 12+150+1 133+30+149


Thank you!

Answer

You should use aggregation function and group by

  "SELECT receptie.id
    , receptie.marca_tel
    , receptie.model
    , receptie.data_primire
    , sum(articole_service.pret_sol=
    , sum(articole_service.pret_achizitie)
    , sum(articole_service.pret_sol ) - sum(articole_service.pret_achizitie) as profit
    FROM receptie 
    inner join articole_service on receptie.id = articole_service.id_receptie 
    group by receptie.id
    , receptie.marca_tel
    , receptie.model
    , receptie.data_primire
    order by receptie.id desc"
Comments