Member Avatar for lewashby 
+--------------+------+------------------------------------+-------+
| ProductCode  | Qty  | Title                              | Price |
+--------------+------+------------------------------------+-------+
| relationaldb | NULL | The Relational Database Dictionary | 14.99 | 
| artofsql     |   50 | The Art of SQL                     | 44.99 | 
| databaseid   |    0 | Database in Depth                  | 29.95 | 
| mysqlspp     |    5 | MySQL Stored Procedure Programming | 44.99 | 
| sqlhks       |   32 | SQL Hacks                          | 29.99 | 
| sqltuning    |  105 | SQL Tuning                         | 39.95 | 
+--------------+------+------------------------------------+-------+

The table above was selected from the following select statement.

mysql> SELECT P.ProductCode, MAX(I.QuantityInStock) as Qty, P.Title, P.Price
FROM Products as P 
LEFT JOIN Inventory as I on (P.ProductCode = I.ProductCode) 
GROUP BY I.ProductCode, P.Title, P.Price;

What is the max function and the group by statements really doing?

Edited by lewashby because: n/a
  • 2 Contributors
  • 1 Reply
  • 99 Views
  • 3 Weeks Discussion Span
  • Latest Post Latest Post by xandro
Member Avatar for xandro

Hi,

Could you post the table schema, what are the field-types and how does the data look like in in raw form, (Not in a selected state.)

Group by is used to group multiple rows of data consolidated into one row with the total data of all rows combined into one.

Say you have 4 rows of the same P.ProductCode, with different types of data, you then use group by to combine these into one row grouped by the P.ProductCode field.

The MAX() mysql function returns the highest of a field, say multiple rows of integers: 1, 2, 4, 10 the MAX function would return 10.

Hope this helps.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.