How to add auxiliary column inside query?-CodePudding

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!

CodePudding user response：

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

CodePudding user response：

if your database engine support partitioning:

select * , count(*) over (partition by Email) as OrderCount
from tablename