CODEWITHSUNDEEP
phpmysqlselectjoingroup-by
Ali Hamra
Ali Hamra

Reputation: 232

group repetitive values

I've the following table called store_items

id store item qty cost sell date
1  A1     aa   12  5   10   2016-04-06
2  A2     cc   10  6   12   2016-04-06 // Store A2 and A3 have same item 
3  A1     bb   15  5   13   2016-04-01 // Store A1 and A3 have same item
4  A3     bb   10  5   13   2016-04-06
5  A3     cc   22  6   12   2016-04-06
6  A1     dd   17  2   12   2016-04-02
7  A1     ee   10  5   10   2016-04-01

Now what I'm expecting the output to be like :

Item cost sell A1 A2 A3
aa   5    10   12 0  0
bb   5    13   15 0  10
cc   6    12   0  10 22
dd   2    12   17 0  0
ee   5    10   10 0  0

As you can see that each item is shown once and its qty are listed below each store, I'm currently feeling lost on how to use group in query Code:

$allstore = $_POST['store']; //Store Name are collected from FORM submit

echo "<table>";
echo "<tr><td align=center>Item Number</td><td align=center>Cost</td><td align=center>Sell</td>";

foreach($allstore as $store => $value){ 
//I've used foreach to find out which store being selected from checkbox form
    echo "<td align=center>".$value."</td>";
}

echo "</tr>";


    $query = "SELECT * from store_items GROUP by item"; //??
    $result = mysql_query($query);

    while($row = mysql_fetch_assoc($result)){

        echo "<tr><td align=center>{$row['item']}</td><td align=center>{$row['cost']}</td><td align=center>{$row['sell']}</td>";

    foreach($allstore as $store => $value){
    echo "<td align=center>".$row['qty']."</td>";
}

    //End of While loop 
    }
echo "</tr>";

Upvotes: 1

Views: 41

Answers (1)

Adam Silenko
Adam Silenko

Reputation: 3108

you should use query like this:

SELECT Item, cost, sell
, Sum(CASE WHEN store = 'A1' then qty end) A1
, Sum(CASE WHEN store = 'A2' then qty end) A2
, Sum(CASE WHEN store = 'A3' then qty end) A3
FROM store_items 
GROUP BY Item, cost, sell

EDIT:
query for data:

SELECT Item, cost, sell, store, Sum(qty)
FROM store_items 
GROUP BY Item, cost, sell, store

or

SELECT Item, cost, sell, Sum(qty)
FROM store_items 
WHERE store = 'A1' --for example
GROUP BY Item, cost, sell

Upvotes: 3

Related Questions