SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 27
  1. #1
    SitePoint Guru menuserve's Avatar
    Join Date
    Aug 2004
    Location
    florida
    Posts
    903
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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!

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    MySQL Manual: GROUP BY (Aggregate) Functions

    Code:
    SELECT
      item,
      COUNT(*) AS `qty`,
      AVG(cond) AS `avgcond`
    FROM
      table
    GROUP BY
      item
    Last edited by Dan Grossman; Jan 18, 2010 at 16:45.

  3. #3
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,033
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dan Grossman View Post
    A corrected version of Dan's query.

    Code SQL:
    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.
    Last edited by SpacePhoenix; Jan 18, 2010 at 16:48. Reason: Improve formatting of the query
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  4. #4
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Oops, I was reading the wrong part of the post when typing that. Replaced SUM with COUNT!

  5. #5
    SitePoint Guru menuserve's Avatar
    Join Date
    Aug 2004
    Location
    florida
    Posts
    903
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  6. #6
    SitePoint Guru menuserve's Avatar
    Join Date
    Aug 2004
    Location
    florida
    Posts
    903
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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>";

  7. #7
    SitePoint Guru menuserve's Avatar
    Join Date
    Aug 2004
    Location
    florida
    Posts
    903
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    got it working!

  8. #8
    SitePoint Guru menuserve's Avatar
    Join Date
    Aug 2004
    Location
    florida
    Posts
    903
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  9. #9
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,501
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    You need to JOIN the two tables.

  10. #10
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,033
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by menuserve View Post
    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.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  11. #11
    SitePoint Guru menuserve's Avatar
    Join Date
    Aug 2004
    Location
    florida
    Posts
    903
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Time to start working on the JOIN function!

    thanks for the direction

  12. #12
    SitePoint Guru menuserve's Avatar
    Join Date
    Aug 2004
    Location
    florida
    Posts
    903
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    why didn't someone warn me that using JOIN is quite complex to standard select queries!

  13. #13
    SitePoint Guru menuserve's Avatar
    Join Date
    Aug 2004
    Location
    florida
    Posts
    903
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    do i combine this with the select query above or should it be a separate statement after?

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by menuserve View Post
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  15. #15
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,501
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Code MySQL:
    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

  16. #16
    SitePoint Guru menuserve's Avatar
    Join Date
    Aug 2004
    Location
    florida
    Posts
    903
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  17. #17
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,501
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    ON table1.item = table2.item
    AND table1.mfr = table2.mfr

  18. #18
    SitePoint Guru menuserve's Avatar
    Join Date
    Aug 2004
    Location
    florida
    Posts
    903
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SELECT
    table1.item

    does this mean

    SELECT
    table1.table2

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

  19. #19
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,501
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    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.

  20. #20
    SitePoint Guru menuserve's Avatar
    Join Date
    Aug 2004
    Location
    florida
    Posts
    903
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  21. #21
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,501
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by menuserve View Post
    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?

  22. #22
    SitePoint Guru menuserve's Avatar
    Join Date
    Aug 2004
    Location
    florida
    Posts
    903
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  23. #23
    SitePoint Guru menuserve's Avatar
    Join Date
    Aug 2004
    Location
    florida
    Posts
    903
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!

  24. #24
    SitePoint Guru menuserve's Avatar
    Join Date
    Aug 2004
    Location
    florida
    Posts
    903
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  25. #25
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,501
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Code MySQL:
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •