CODEWITHSUNDEEP
phpmysqlsqldatabase
arkate
arkate

Reputation: 501

Grouping display data from MySQL

I have a table like such:

     Boxes     Items         Weight (kgs)
   ==================================
     101        CHICKEN       2 
     102        CHICKEN       3 
     104        CHICKEN       4 
     105        BEEF          3

Is it possible, in MySQL and PHP, to display this data in the following format for an HTML table?

Boxes                 Items
=====================================
101-104               9kgs of CHICKEN
105                   3kgs of BEEF

Thanks.

Upvotes: 1

Views: 52

Answers (5)

Martin
Martin

Reputation: 923

SELECT min(boxes),max(boxes),items,sum(weight) GROUP BY items

But you have a few problems, your table seems to contain text and not numbers, you'll need to fix that so functions like MIN, MAX, SUM, AVG work. Second, your box numbers are (or may be) non-contiguous, so you probably want to handle that separately. The logic of that is a bit more complicated. E.g. what if you have box 200 and 203 with chickens and 201 and 204 with beef, and 202,205 pork, if it says 200-203 is chicken, then it's wrong. Then again if you have many boxes the list will be large. That's half-way into user interface design land.

Good luck.

Upvotes: 0

Brian DeMilia
Brian DeMilia

Reputation: 13248

select concat(min(boxes), '-', max(boxes)) as boxes,
       concat(sum(weight), 'kgs of ', items) as items
  from tbl
 group by items

SQL fiddle: http://sqlfiddle.com/#!2/07850/4/0

Upvotes: 0

Sammitch
Sammitch

Reputation: 32232

SELECT GROUP_CONCAT(Boxes), Item, SUM(Weight)
FROM Inventory
GROUP BY Item

Will give you:

Boxes          Items     Weight
=====================================
101,102,104    CHICKEN   9
105            BEEF      3

Upvotes: 0

chofer
chofer

Reputation: 357

    $query = " select group_concat(Boxes) as box,sum(weight) as weight , item 
             From your_table
             group by item";

 $result = $mysqli->query($query);

 echo "<table>
         <thead> 
          <th>Boxes</th>
          <th>Items</th>
        </thead>";
 while($row = $result->fetch_array())
 {
       echo "<tr>";
          echo "<td>".$row["box"]."</td>";
          echo "<td>".$row["weight"]." kg  of ".$row["item"]."</td>";
       echo "</tr>"; 
 }
   echo "</table>";

Weight field should be a number

Upvotes: 0

Barmar
Barmar

Reputation: 780909

SELECT CASE COUNT(*)
        WHEN 1 THEN boxes
        ELSE CONCAT(MIN(boxes), '-', MAX(BOXES))
       END AS Boxes,
       CONCAT(SUM(Weight), 'kgs of ', items) AS Items
FROM yourTable
GROUP BY Items

Upvotes: 2

Related Questions