My table might look like this:
| Email | ID | Order_date | Total |
| ----------------- | --------- | ------------ | ------------ |
|customerA@gmail.com| 1 | 01-01-2019 | 500 |
|customerB@gmail.com| 2 | 02-01-2019 | 1000 |
|customerC@gmail.com| 3 | 03-01-2019 | 1000 |
|customerD@gmail.com| 4 | 04-01-2019 | 2000 |
|customerA@gmail.com| 5 | 05-01-2019 | 3000 |
|customerB@gmail.com| 6 | 06-01-2019 | 500 |
|customerC@gmail.com| 7 | 03-01-2019 | 1000 |
|customerA@gmail.com| 8 | 05-01-2019 | 3000 |
|customerB@gmail.com| 9 | 09-01-2019 | 2000 |
|customerA@gmail.com| 10 | 10-01-2019 | 4000 |
|customerB@gmail.com| 11 | 02-01-2019 | 1000 |
|customerA@gmail.com| 12 | 12-01-2019 | 2000 |
I'd like to add an auxiliary column with number of orders, but without any additional grouping. My dream table would look like this:
| Email | ID | Order_date | Total | Number of orders |
| ----------------- | --------- | ------------ | ------------ | ---------------- |
|customerA@gmail.com| 1 | 01-01-2019 | 500 | 5 |
|customerB@gmail.com| 2 | 02-01-2019 | 1000 | 4 |
|customerC@gmail.com| 3 | 03-01-2019 | 1000 | 2 |
|customerD@gmail.com| 4 | 04-01-2019 | 2000 | 1 |
|customerA@gmail.com| 5 | 05-01-2019 | 3000 | 5 |
|customerB@gmail.com| 6 | 06-01-2019 | 500 | 4 |
|customerC@gmail.com| 7 | 03-01-2019 | 1000 | 2 |
|customerA@gmail.com| 8 | 05-01-2019 | 3000 | 5 |
|customerB@gmail.com| 9 | 09-01-2019 | 2000 | 4 |
|customerA@gmail.com| 10 | 10-01-2019 | 4000 | 5 |
|customerB@gmail.com| 11 | 02-01-2019 | 1000 | 4 |
|customerA@gmail.com| 12 | 12-01-2019 | 2000 | 5 |
As you can probably guess I would like to perform further calculations (like sum or average) based on the number of shopping orders. My SQL query could look like this:
SELECT AVG(Total)
FROM
(SELECT ...
...
...
WHERE ...
...
...
GROUP BY ...
HAVING COUNT(Number_of_orders > 1) AND COUNT(Number_of_orders < 5) x
Or something like that. Just to give you the basic idea why I think I need the auxiliary column.
I would have thought this is easy, but I've spent many hours on it already, so any kind of advice or help would be appreciated!
Assuming that 'Email' is the column you want to group by the number of orders, this should get you your desired table.
SELECT T.Email, T.ID, T.Date, T.Total, TT.Count
FROM Table T
INNER JOIN (
SELECT Email, Count(Email) as Count
FROM Table
GROUP BY Email) TT
ON T.Email = TT.Email