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:
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
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
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?
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):
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