need help with a query and subsequent output

[menuserve]

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!

[Dan Grossman]

MySQL Manual: GROUP BY (Aggregate) Functions

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

[SpacePhoenix]

MySQL Manual: GROUP BY (Aggregate) Functions

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.

[Dan Grossman]

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

[menuserve]

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.

[menuserve]

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 `avgcond`FROM 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>";

[menuserve]

got it working!

[menuserve]

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 "&#37; 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?

[guido2004]

You need to JOIN the two tables.

[SpacePhoenix]

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.

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

[menuserve]

Time to start working on the JOIN function!

thanks for the direction

[menuserve]

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

[menuserve]

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

[r937]

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

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

[guido2004]

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

[menuserve]

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?

[guido2004]

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

[menuserve]

SELECT
table1.item

does this mean

SELECT
table1.table2

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

[guido2004]

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.

[menuserve]

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 `avgcond`FROM 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

[guido2004]

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

Did you try my variation?
Can you post your query with the JOIN?
And can you post the error it's giving you?

[menuserve]

here is the query:

SELECT certnumbers.setdescription , COUNT(*) AS qty , AVG(certnumbers.grade) AS avgcond, , (COUNT(*) / sets.setqty) * 100 AS pcomp FROM certnumbers INNER JOIN sets ON certnumbers.setdescription = sets.item AND certnumbers.sport = sets.sport GROUP BY certnumbers.setdescription

This is the error:

Warning: mysql_error(): supplied argument is not a valid MySQL-Link resource in /usr/local/psa/home/vhosts/verisleeve.com/httpdocs/admin/mysql_send-4.php on line 28

[menuserve]

I got it working...

SELECT
certnumbers.setdescription
, COUNT(*) AS qty
, AVG(certnumbers.grade) AS avgcond
, (COUNT(*) / sets.setqty) * 100 AS pcomp
FROM certnumbers
INNER JOIN sets
ON certnumbers.setdescription = sets.setdescription
AND certnumbers.sport = sets.sport
GROUP BY
certnumbers.setdescription

Now to add a where clause and add to the php output!

[menuserve]

In the certnumbers table I have a column called owner. I'm having issues getting a where clause to work as follows:

SELECT
certnumbers.setdescription
, COUNT(*) AS qty
, AVG(certnumbers.grade) AS avgcond
, (COUNT(*) / sets.setqty) * 100 AS pcomp
FROM certnumbers
WHERE owner=ekramer
INNER JOIN sets
ON certnumbers.setdescription = sets.setdescription
AND certnumbers.sport = sets.sport
GROUP BY
certnumbers.setdescription

[guido2004]

SELECT
certnumbers.setdescription
, COUNT(*) AS qty
, AVG(certnumbers.grade) AS avgcond
, (COUNT(*) / sets.setqty) * 100 AS pcomp
FROM certnumbers
INNER JOIN sets
ON certnumbers.setdescription = sets.setdescription
AND certnumbers.sport = sets.sport
WHERE owner='ekramer'
GROUP BY
certnumbers.setdescription

You really should study the syntax a bit more