CODEWITHSUNDEEP
sqlgoogle-bigquery
Aleko
Aleko

Reputation: 103

Join two tables with pre aggregated Values results in neither grouped nor aggregated error

I have been trying to join tables one includes the UserId the other one contains a row for each action a user has taken. The goal is to aggregate the actions a user has taken each day.

Table 1:

| UserId   | date           |
| -------- | -------------- |
| 13       | 2021-08-04     |
| 13       | 2021-08-05     |
| 13       | 2021-08-06     |
| 14       | 2021-08-04     |
| 14       | 2021-08-05     |
| 14       | 2021-08-06     |
| 15       | 2021-08-05     |
| 15       | 2021-08-06     |

Table 2:

UserID ActionDate ActionVal
13 2021-08-04 2.6
13 2021-08-04 5
13 2021-08-05 15
14 2021-08-05 7
14 2021-08-05 2
14 2021-08-05 0
15 2021-08-05 13
15 2021-08-06 27

My query:

SELECT Table1.UserId,Table1.date,aggTable2.tdTotalValue, aggTable2.controlDate, aggTable2.controlUserId

FROM Table1
JOIN(
SELECT
SUM(ActionVal) tdTotalValue,
ActionDate controlDate
UserId controlUserId
FROM Table2
GROUP BY ActionDate, UserId 
) as aggTable2
ON aggTable2.controlDate = Table1.date AND Table1.UserId = aggTable2.controlUserId
GROUP BY aggTable2.controlDate,Table1.UserId

However, this results in returning the Error: SELECT list expression references aggTable2.tdTotalValue which is neither grouped nor aggregated at 1:1

This is really odd to me as I have pre-aggregated the table to make sure that this doesn't occur.

Im lost and thankful for all advise.

Upvotes: 0

Views: 405

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173046

As it is looks like you already took care of aggregation in aggTable2 subquery - you should just remove GROUP BY aggTable2.controlDate,Table1.UserId (in last line of your query)

If you do so, the result output will be

enter image description here

I used below query as a test (which gave above output)

SELECT 
  Table1.UserId,
  Table1.date,
  aggTable2.tdTotalValue, 
  aggTable2.controlDate, 
  aggTable2.controlUserId
FROM Table1
JOIN (
  SELECT
    SUM(ActionVal) tdTotalValue,
    ActionDate controlDate,
    UserId controlUserId
  FROM Table2
  GROUP BY ActionDate, UserId 
) as aggTable2
ON aggTable2.controlDate = Table1.date 
AND Table1.UserId = aggTable2.controlUserId

Note: this is assumed you do not have duplicates in Table 1 - which is seems to be true based on sample data

Upvotes: 1

Related Questions