Need help with a query and subsequent output

I’m new to mysql and php and need some help if possible!

I have a table that looks like the following –

ID - ITEM – COND
1 – Hat – 1
2 – Shirt – 5
3 – Pants – 10
4 – Hat – 4
5 – Hat – 9
6 – Hat – 6
7 - Shirt – 5

I want to be able to pull from the table a list of UNIQUE items, how many of each unique item there are, and their average condition…
Thus the output should be:

ITEM – QTY – AVG COND
Hat – 4 - 5
Pants – 1 - 10
Shirt – 2 – 7.5

Any help would be appreciated!

MySQL Manual: GROUP BY (Aggregate) Functions

SELECT
  item,
  COUNT(*) AS `qty`,
  AVG(cond) AS `avgcond`
FROM
  table
GROUP BY
  item

A corrected version of Dan’s query.

SELECT
      item
    , COUNT(item) AS qty
    , (SELECT AVG(cond) FROM items) AS avgcond
FROM
    items
GROUP BY
    item

@Dan, your query does not work as with the table structure provided by the OP, there is no field called qty. It has nothing to SUM up.

Oops, I was reading the wrong part of the post when typing that. Replaced SUM with COUNT!

dan,

that works like a charm! the avg cond is a crazy decimal, I’ll have to read on keeping it to 2 decimal places.

dan,

that works like a charm! At least it does in the dbquery… but when i try to output to the web, i get nothing… I’m doing something wrong here…

$result = mysql_query(“SELECT item, COUNT(*) AS qty, AVG(cond) AS avgcondFROM items GROUP BY item”);

echo “<table border=0><tr>”;

while($row = mysql_fetch_array($result))
{
echo"<td>$item</td><td>$qty</td><td>$avgcond</td>";
}
echo “</tr></table>”;

got it working!

i have another table that has two columns…

item & mqty

mqty is max quantity i can hold in the inventory for the item.

i’m trying to figure out how to add a “% of max inv” column to my output above. Somehow i need to cross reference the QTY in one table with the MQTY in the other, calculate the % and display

any ideas?

You need to JOIN the two tables.

The round() function in php is what you need for that.

Time to start working on the JOIN function!

thanks for the direction

why didn’t someone warn me that using JOIN is quite complex to standard select queries!

do i combine this with the select query above or should it be a separate statement after?

actually, doing a join in SQL is a ~lot~ simpler than combining data from separate tables using application logic – and a lot more efficient, too

:slight_smile:


SELECT
    table1.item
  , COUNT(*) AS qty
  , AVG(table1.cond) AS avgcond,
  , (COUNT(*) / table2.mqty) * 100 AS maxinv
FROM table1
INNER JOIN table2
ON table1.item = table2.item
GROUP BY
    table1.item

guido… thanks… one more question…

i just realized my items may not be unique… its the combination of the item field and the mfr field that make it unique.

is there a way to modify the two tables to join based on the combination of “item & mfr” in one table to the matching “item & mfr” in the other instead of just based on item?

ON table1.item = table2.item
AND table1.mfr = table2.mfr

SELECT
table1.item

does this mean

SELECT
table1.table2

or does this change now that we are selecting the item and mfr

No, it means column ‘item’ from ‘table1’.
Since two tables have a column with this same name, if you don’t specify which one you’re talking about, MySQL will throw an error.

wow… i’m running into all sorts of errors any way i try…

Currently this works for me (different col names than example above)

$result2 = mysql_query(“SELECT setdescription, sport, COUNT(*) AS qty, AVG(grade) AS avgcondFROM certnumbers WHERE owner=‘{$_SESSION[‘logname’]}’ GROUP BY setdescription”);

With the following line i provide my output (sport, set, qty, avgcond):

echo"<tr><th>" . $row[sport] . “<th>” . $row[setdescription] . “</th><th align=‘right’>” . $row[qty] . “</th><th align=‘right’>52%</th><th align=‘right’>” . round($row[avgcond], 2) . “</th></tr>”;

NEXT - What I’m trying to do now is join the second table that has ‘sport’ ‘setdescription’ and ‘mqty’ and then add a column that shows the % complete when it calculates the qty against the mqty for the matching ‘sport’ and ‘setdescription’ combos

I’ve tried a number of variations using the JOIN and everything seems to error/fail