June 14th, 2013, 10:15 AM
-
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.
June 14th, 2013, 10:23 AM
-
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."
June 14th, 2013, 10:28 AM
-
Hi Jyncka.
I have 2 different category_id in the category table.
June 14th, 2013, 10:43 AM
-
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."
June 14th, 2013, 11:22 AM
-
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
June 14th, 2013, 01:44 PM
-
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
June 15th, 2013, 04:56 PM
-
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 ?
June 15th, 2013, 05:34 PM
-
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.
June 15th, 2013, 07:07 PM
-
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
June 16th, 2013, 01:37 PM
-
Thanks for your help guys.
I am new to SQL and PHP so apologies for my ignorance.