#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    5
    Rep Power
    0

    GROUP BY will only return one row


    Hi there.

    I wonder if someone can point me in the right direction here.

    I have a database that holds 2 tables with some rows as below:


    Category
    category_id,
    category_name


    Product
    id,
    items,
    category_id,
    price


    I am trying to display each category_name with a list of corresponding items beneath it.

    My SQL query is as follows:

    PHP Code:
    $sql=”SELECT FROM product AS p
        LEFT JOIN category 
    AS c
        ON p
    .category_id=c.category_id
        GROUP BY c
    .category_name"; 
    Unfortunately this only displays each category_name with one item (row) beneath.

    As a complete newcomer to SQL, I am hoping this is due to something obvious.

    Not sure if this is relevant but I used phpMyAdmin (on XXAMP) to build the database.

    I have tried the query without the alias’s also, but my error report advised me to use them.

    Any advice would be sincerely appreciated.
  2. #2
  3. Put a potato on it!
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2008
    Location
    Maryland
    Posts
    304
    Rep Power
    971
    How many different category ids do you have in the Category table? If you only have one row, it'll return one row. Group by condenses the result set into rows that represent a group of rows, determined by the column you're grouping by. So if you have one category id in the Category table and you're grouping by that as you are in the query, you will only get one row back.
    "Those who can make you believe absurdities can make you commit atrocities."
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    5
    Rep Power
    0
    Hi Jyncka.

    I have 2 different category_id in the category table.
  6. #4
  7. Put a potato on it!
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2008
    Location
    Maryland
    Posts
    304
    Rep Power
    971
    Originally Posted by mr_Boombastic
    Hi Jyncka.

    I have 2 different category_id in the category table.
    Sorry, I misread your post a little and you did say
    Originally Posted by mr_Boombastic
    Unfortunately this only displays each category_name with one item (row) beneath.
    It sounds like group by isn't what you need, a more advanced user can correct me on this, but you could try creating a pivot table which would allow you select rows from the Category table and use them as columns with corresponding rows for each category id.

    Here is a guide on doing this: pivot tables
    "Those who can make you believe absurdities can make you commit atrocities."
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    5
    Rep Power
    0
    Thanks Jyncka.

    Its a shame. I was kind of hoping there would be a magic line I could type to make it work !

    Having had a cursory glance at pivot tables, it looks like I will need to get reading
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,178
    Rep Power
    4274
    Originally Posted by mr_Boombastic
    I am trying to display each category_name with a list of corresponding items beneath it.
    what you are looking for is ORDER BY, not GROUP BY

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    5
    Rep Power
    0
    Hi r937.

    I need the category to show just once with the products below it.

    ORDER BY will list the category name each time a row is returned.

    For example:

    JEANS
    Pepe Jeans
    JEANS
    Levi

    TRAINERS
    Nike
    TRAINERS
    Addidas
    TRAINERS
    Puma

    Unless I can put a DISTINCT somewhere in my sql ?
  14. #8
  15. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    database systems are not made for displaying pretty tables. Their sole purpose is to store and provide data. How this data gets formatted is up to the application.

    So if you wanna display those categories and products in a certain way, you loop through the rows with PHP (or whatever language you're using). And every time you have a new category, you generate this category heading.

    That's it. No "DISTINCT", no "GROUP BY". Just order the rows by category and do the formatting with your scripting language.

    Note that the way you're using "GROUP BY" makes no sense and is actually wrong. MySQL lets you do it, but on other database systems, this wouldn't even work. You'd simply get an error message.

    The sole purpose of "GROUP BY" is to apply an aggregate function like COUNT(), SUM() etc. to certain groups of rows rather than all rows as a whole. It's not "grouping" in the sense of: I have one category with many elements underneath it. That's not what "GROUP BY" means. As a rule of thumb: If you have a "GROUP BY" in your query but no aggregate function, you're using it wrong. You probably want "ORDER BY" or "DISTINCT".

    When using "GROUP BY", you can only select aggregate expressions and the columns listed in the "GROUP BY" clause. You cannot select other columns, because there's simply no definite value for them.

    Take your example: The JEANS "group" has two products, Pepe Jeans and Levi's. Now, what is the product your database system is supposed to display for the JEANS "group"? Pepe Jeans? Levi's? Nobody knows.

    Like I said, MySQL lets you do it and will simply choose an arbitrary value -- making thousands of MySQL users believe this is how "GROUP BY" works. But that's wrong. It violates the SQL standard* and only works in MySQL (whether you call it a bug or a feature is up to you).



    *Actually, the new SQL standard from 2003 will also let you select columns which are functionally dependent on the "GROUP BY" columns, because those do have a definite value. But that's a different topic.
    Last edited by Jacques1; June 15th, 2013 at 05:41 PM.
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,178
    Rep Power
    4274
    Originally Posted by Jacques1
    So if you wanna display those categories and products in a certain way, you loop through the rows with PHP (or whatever language you're using). And every time you have a new category, you generate this category heading.
    nicely explained

    and it's worth repeating that ORDER BY is crucial for this strategy

    Originally Posted by Jacques1
    *Actually, the new SQL standard from 2003 will also let you select columns which are functionally dependent on the "GROUP BY" columns, because those do have a definite value. But that's a different topic.
    and i would call it an advanced topic

    nice post, jacques

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    5
    Rep Power
    0
    Thanks for your help guys.

    I am new to SQL and PHP so apologies for my ignorance.

IMN logo majestic logo threadwatch logo seochat tools logo