Page 1 of 1

GROUP BY / HAVING Question

Posted: Sat Dec 14, 2002 9:25 pm
by TheTestUser
I'm having quite a lot of difficulty trying to retrieve the information I need from my data set. Here's a simple example of what I am talking about.

Code: Select all

mysql> SELECT UserID, Purchases FROM test;
+--------+-----------+
| UserID | Purchases |
+--------+-----------+
|      1 |         1 |
|      2 |         4 |
|      3 |         7 |
|      4 |        14 |
|      5 |        55 |
|      6 |       642 |
+--------+-----------+

mysql> SELECT OrdersMade, Level FROM test2;
+------------+------------------------+
| OrdersMade | Level                  |
+------------+------------------------+
|          0 | New Shopper            |
|          5 | Eager Shopper          |
|         10 | Casual Shopper         |
|         15 | Regular Shopper        |
|         50 | Loyal Consumer         |
|        100 | College Tuition Backer |
+------------+------------------------+
Those are two sample tables. They are not what I'm working with but it sums up my problem.
I can get what level one user is at by doing a query like this:

Code: Select all

mysql> SELECT UserID, Purchases, OrdersMade, Level
    -> FROM test, test2
    -> WHERE Purchases >= OrdersMade and UserID = 5
    -> ORDER BY OrdersMade DESC;
+--------+-----------+------------+-----------------+
| UserID | Purchases | OrdersMade | Level           |
+--------+-----------+------------+-----------------+
|      5 |        55 |         50 | Loyal Consumer  |
|      5 |        55 |         15 | Regular Shopper |
|      5 |        55 |         10 | Casual Shopper  |
|      5 |        55 |          5 | Eager Shopper   |
|      5 |        55 |          0 | New Shopper     |
+--------+-----------+------------+-----------------+
I usually append it with LIMIT 1, but I left it there to show you that with a query like this, I'm not only getting rows that contain my info, but also of all the levels below that.

I would like to print out a table that contains all users and their userlevels next to them so the output would look similar to this:

Code: Select all

+--------+-----------+------------------------+
| UserID | Purchases | Level                  |
+--------+-----------+------------------------+
|      1 |         1 | New Shopper            |
|      2 |         4 | New Shopper            |
|      3 |         7 | Eager Shopper          |
|      4 |        14 | Casual Shopper         |
|      5 |        55 | Loyal Consumer         |
|      6 |       642 | College Tuition Backer |
+--------+-----------+------------------------+
I've tried to do this by playing with the GROUP BY operator.

Code: Select all

SELECT UserID, Purchases, OrdersMade, Level
FROM test, test2
GROUP BY UserID
HAVING OrdersMade = MAX(OrdersMade)
That didn't work.

Code: Select all

mysql> SELECT UserID, Purchases, OrdersMade, Level
    -> FROM test, test2
    -> WHERE OrdersMade <= Purchases
    -> GROUP BY UserID
    -> ORDER BY UserID ASC, OrdersMade DESC;
+--------+-----------+------------+-------------+
| UserID | Purchases | OrdersMade | Level       |
+--------+-----------+------------+-------------+
|      1 |         1 |          0 | New Shopper |
|      2 |         4 |          0 | New Shopper |
|      3 |         7 |          0 | New Shopper |
|      4 |        14 |          0 | New Shopper |
|      5 |        55 |          0 | New Shopper |
|      6 |       642 |          0 | New Shopper |
+--------+-----------+------------+-------------+
Didn't yield the results I was expecting either.

I'm really just learning SQL so bear with me if this sounds stupid.
I'd like to know WHY it doesn't work or if there is a workaround.

Posted: Sun Dec 15, 2002 9:21 pm
by Rob the R
The right way to do this would be with a sub-select, but MySQL does not support them, so I think you're left with needing 2 separate queries: the first to select each user, then the second to select that user's level.

As to what happened with the following query (I added table aliases so it's easier to tell what column is from what table):

Code: Select all

SELECT u.UserID, u.Purchases, l.OrdersMade, l.Level 
FROM test u, test2 l 
WHERE l.OrdersMade <= u.Purchases 
GROUP BY u.UserID 
ORDER BY u.UserID ASC, l.OrdersMade DESC;
The general rule of thumb I follow is to only select and sort by columns that are in the "group by" clause (you can also select aggregate functions like MAX() and COUNT()), otherwise you'll get unexpected results.

The main thing here is that it performs the "group by" before the "order by". So when it eliminates all rows except for those unique values for the columns in your order by clause "UserID", there is no good way for the query to pick which values of OrdersMade and Level to display. Purchases is OK, since it is associated with the UserID. The "order by OrdersMade DESC" is meaningless, because it has already picked the value for OrdersMade more or less arbitrarily.

So until MySQL supports sub-queries, you'll have to resort to having two separate queries for things like this.