1

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!

Amberjack
  • 45
  • 5

2 Answers2

1

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
Hichame Yessou
  • 2,658
  • 2
  • 18
  • 30
  • 1
    Fantastic! My actual query is much longer (I am joining three tables and there's a lot of WHERE conditions), but after a few attempts I have managed to use your idea with INNER JOIN. This would take me a lot of time to figure it out myself, I'm pretty sure - I am a noob:-) Thankfully, I am not working with SQL on a daily basis, although it is kinda fun for me I have to admit. Anyway, thank you! – Amberjack Nov 01 '21 at 18:56
1

if your database engine support partitioning:

select * , count(*) over (partition by Email) as OrderCount
from tablename
eshirvana
  • 23,227
  • 3
  • 22
  • 38
  • Thank you for your input! The problem is in SOQL (Salesforce SQL) is forbidden to use "SELECT *". Not sure why. Probably due to some computational issues. – Amberjack Nov 01 '21 at 19:05