Asked  7 Months ago    Answers:  5   Viewed   38 times

Building an inventory system. I have lots of products and each product has three different variables. So for stock totals I want to group by the two columns (product & size) and sum quantity to get stock total.

product Size Quantity
Widget one 2 275
Widget one 2 100
Widget two 3 150
Widget two 2 150

What I want for output:

product Size Quantity
Widget one 2 375
Widget two 3 150
Widget two 2 150

I figured out how to group by one column and sum using the code below:

$query = "SELECT product, SUM(Quantity) FROM inventory GROUP BY product";  
$result = mysql_query($query) or die(mysql_error());
// Print out result
while($row = mysql_fetch_array($result)){
    echo "Total ". $row['product']. " = ". $row['SUM(Quantity)'];
    echo "<br />";
}
?>

I am just stuck on grouping by both columns. Is it possible? or should I just create three different products for the of the three sizes and eliminate that column? Thanks.

 Answers

44

Based on your example table, it appears you want to be grouping on product rather than id. You merely need to add the Size column to both the SELECT list and the GROUP BY

$query = "SELECT 
            product,
            Size, 
            SUM(Quantity) AS TotalQuantity 
          FROM inventory
          GROUP BY product, Size";

Note that I have added a column alias TotalQuantity, which will allow you to more easily retrieve the column from the fetched row via the more sensible $row['TotalQuantity'], rather than $row['SUM(Quantity)']

Wednesday, March 31, 2021
 
Gerardo
answered 7 Months ago
71

you can't print the result from mysqli_query, it is mysqli_resource and for dumping the error you need to change mysql_error() to mysqli_error()

$username = "bob";
$db = mysqli_connect("localhost", "username", "password", "user_data");
$sql1 = "select id from user_information where username='$username'";
$result = mysqli_query($db, $sql1) or die(mysqli_error());
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) { 
    echo $row['id'].'<br>'; 
} 
Saturday, May 29, 2021
 
pamelus
answered 5 Months ago
39

yo need create the user "pma" in mysql or change this lines(user and password for mysql):

/* User for advanced features */
$cfg['Servers'][$i]['controluser'] = 'pma'; 
$cfg['Servers'][$i]['controlpass'] = '';

Linux: /etc/phpmyadmin/config.inc.php

Tuesday, July 13, 2021
 
ShadowZzz
answered 3 Months ago
24

Please try:

SELECT
    Code,
    SUM(ItemCount) ItemCount,
    Type,
    SUM(Amount) Amount
FROM
    YourTable
GROUP BY Code, Type
ORDER BY Code
Friday, July 23, 2021
 
Stefan
answered 3 Months ago
24

Your query is fine. The reason you are getting 2000 rows is because you are getting one row for every unique pair of values user_id, item_id.

If you want to see the interaction types going into each row then use:

select user_id, item_id, max(interaction_type) as max_type,
       group_concat(distinct interaction_type) as interaction_types,
       count(*) as cnt
from mytable
group by user_id, item_id;

It occurs to me that you want all rows with the maximum interaction type. If so, calculate the maximum and then find all rows that match that value:

select t.*
from mytable t cross join
     (select max(interaction_type) as maxit from mytable) x
     on x.maxit = t.interaction_type;

No group by is needed for this query.

Wednesday, September 1, 2021
 
Trott
answered 2 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :