GROUP BY / HAVING Question
Posted: Sat Dec 14, 2002 9:25 pm
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.
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:
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:
I've tried to do this by playing with the GROUP BY operator.
That didn't work.
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.
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 |
+------------+------------------------+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 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 |
+--------+-----------+------------------------+Code: Select all
SELECT UserID, Purchases, OrdersMade, Level
FROM test, test2
GROUP BY UserID
HAVING OrdersMade = MAX(OrdersMade)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 |
+--------+-----------+------------+-------------+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.