SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot wh33t's Avatar
    Join Date
    Aug 2011
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Select some data while counting entries from a different table?

    Hey SP,

    I've been trying to learn how to use Mysql more effectively in hopes that it will continue to reduce the amount of PHP I have to write to get data from the database. It's been quite rewarding so far and now I have a new challenge I'm trying to over come.

    I have a table called "categories" and a table called "items". The categories are differentiated (or w/e word I'm supposed to be using) via unique, auto-incremented category_id of int11. The items are differentiated via item_id of int11 as well and the items table has a column called category_id which is a foreign_key? of categories.

    So what I'd like to do is select all categories and count the amount of items in that category by doing something like "select * from categories and "item_count" as (select count(*) from items where category_id = xxxx)" in which case the result will yield an array in PHP that gives me access to "item_count" as $array['item_count'] as well as all of the columns of the table "categories".

    I've tried reading some tutorials from the web and I'm confident I could get this working but that doesn't mean I'll understand what it is I'm doing and I find that to be rather pointless and I've had such good results with what I learn here on sitepoint.

    Thanks for reading and any tips you share.

  2. #2
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    4,823
    Mentioned
    142 Post(s)
    Tagged
    0 Thread(s)
    You want to join the tables and group them.

    Code:
    SELECT category_id, name, COUNT(item_id) AS 'item_count' FROM categories AS c 
        LEFT JOIN items AS i ON c.category_id = i.category_id 
        GROUP BY category_id, name
    Be sure to congratulate xMog on earning April's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  3. #3
    SitePoint Zealot wh33t's Avatar
    Join Date
    Aug 2011
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cpradio View Post
    You want to join the tables and group them.

    Code:
    SELECT category_id, name, COUNT(item_id) AS 'item_count' FROM categories AS c 
        LEFT JOIN items AS i ON c.category_id = i.category_id 
        GROUP BY category_id, name
    Wow, speedy reply. Would you mind explaining what GROUP BY is doing? I think I get the COUNT(item_id) as 'item_count'.

  4. #4
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    4,823
    Mentioned
    142 Post(s)
    Tagged
    0 Thread(s)
    A join will create a record for each combination, so let's say you have 2 categories and 3 items in each category, so you would get 6 results.
    Example:
    Code:
    Category_ID                 Name                   Item_ID            Item Name                   Price
    ----------------------------------------------------------------------------------------------------------------------------------
    1                           Category 1              1                 Item 1                      5.00
    1                           Category 1              2                 Item 2                      6.00
    1                           Category 1              3                 Item 3                      7.00
    2                           Category 2              4                 Item 4                      8.00
    2                           Category 2              5                 Item 5                      9.00
    2                           Category 2              6                 Item 6                      10.00
    The group by says, I want my results grouped by X, in this case category_id and name, so it will group the results by those columns
    Example:
    Code:
    Category_ID                 Name                   
    -----------------------------------------------------------------------------------------------------------------
    1                           Category 1            
         ----------------------------------------------------------------------
         Item_ID            Item Name                   Price
         ----------------------------------------------------------------------
         1                  Item 1                      5.00
         2                  Item 2                      6.00
         3                  Item 3                      7.00
    
    2                           Category 2            
         ----------------------------------------------------------------------
         Item_ID            Item Name                   Price
         ----------------------------------------------------------------------
         4                  Item 4                      8.00
         5                  Item 5                      9.00
         6                  Item 6                      10.00

    With group by, you can only select the columns you group on. But you can add new columns so long as they are a result of COUNT(), SUM(), AVG(), MIN(), MAX(), etc.
    I used COUNT(item_id), so that it would count the items in the category group, thus providing you with the category id, name and the number of items in that category.

    Thus you end up with
    Code:
    Category_ID                 Name                   item_count
    ----------------------------------------------------------------------------------------------------------------------------------
    1                           Category 1             3
    2                           Category 2             3
    Be sure to congratulate xMog on earning April's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes

  5. #5
    SitePoint Zealot wh33t's Avatar
    Join Date
    Aug 2011
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cpradio View Post
    A join will create a record for each combination, so let's say you have 2 categories and 3 items in each category, so you would get 6 results.
    Example:
    Code:
    Category_ID                 Name                   Item_ID            Item Name                   Price
    ----------------------------------------------------------------------------------------------------------------------------------
    1                           Category 1              1                 Item 1                      5.00
    1                           Category 1              2                 Item 2                      6.00
    1                           Category 1              3                 Item 3                      7.00
    2                           Category 2              4                 Item 4                      8.00
    2                           Category 2              5                 Item 5                      9.00
    2                           Category 2              6                 Item 6                      10.00
    The group by says, I want my results grouped by X, in this case category_id and name, so it will group the results by those columns
    Example:
    Code:
    Category_ID                 Name                   
    -----------------------------------------------------------------------------------------------------------------
    1                           Category 1            
         ----------------------------------------------------------------------
         Item_ID            Item Name                   Price
         ----------------------------------------------------------------------
         1                  Item 1                      5.00
         2                  Item 2                      6.00
         3                  Item 3                      7.00
    
    2                           Category 2            
         ----------------------------------------------------------------------
         Item_ID            Item Name                   Price
         ----------------------------------------------------------------------
         4                  Item 4                      8.00
         5                  Item 5                      9.00
         6                  Item 6                      10.00

    With group by, you can only select the columns you group on. But you can add new columns so long as they are a result of COUNT(), SUM(), AVG(), MIN(), MAX(), etc.
    I used COUNT(item_id), so that it would count the items in the category group, thus providing you with the category id, name and the number of items in that category.

    Thus you end up with
    Code:
    Category_ID                 Name                   item_count
    ----------------------------------------------------------------------------------------------------------------------------------
    1                           Category 1             3
    2                           Category 2             3
    Thank you for the very elaborate explanation. I think I know what you mean by it all, I've just go to learn to think in those terms instead of the way I originally figured the query would be written. Would you say this is the only way to do such a query, or just the best?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by wh33t View Post
    Would you say this is the only way to do such a query, or just the best?
    just the best
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •