0

I have this block of code:

(SELECT SELL_STR.SELL_STR_NBR, DATES.PSTD_FSCL_YR_WK, count(*) as WKLY_DLVRY--, WORK_ORD_NBR
from `analytics-df-thd.DFS.FACTS_DLY_DELV_STATS` A
inner join R1
on r1.FSCL_YR_WK = A.DATES.PSTD_FSCL_YR_WK
group by SELL_STR.SELL_STR_NBR, DATES.PSTD_FSCL_YR_WK
)

I need to add WORK_ORD_NBR to the SELECT so I can later join it

select 
WKLY_DLVRY.WKLY_DLVRY
...
from table1 a
join
WKLY_DLVRY
on WKLY_DLVRY.WORK_ORD_NBR = a.WORK_ORD_NBR

However when added to WKLY_DLVRY, I receive this error:

SELECT list expression references column WORK_ORD_NBR which is neither grouped nor aggregated at

I have read many threads and they didn't give me many options, only suggesting that WORK_ORD_NBR must be grouped, however that changes the count. I appreciate any help

Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230
akurenkov
  • 1
  • 2
  • This looks like a good suggestion ... https://www.kaggle.com/questions-and-answers/103533 Seems that you need to add your column into your "group by" – Kolban Jun 15 '22 at 18:06
  • I cannot do that though, it would change my count(*) – akurenkov Jun 15 '22 at 18:40
  • If you don't get an answer, post to the question a trivial substitute question using some sample data (a CSV) and a simple query that is logically equivalent but removes all the project specific complexity. – Kolban Jun 16 '22 at 00:24

1 Answers1

0

You can keep a column without grouping like below.

For more information please check here.

SELECT
    count(*) as WKLY_DLVRY,
    ARRAY_AGG(
        STRUCT(WORK_ORD_NBR)
        -- ORDER BY [column] DESC LIMIT 1  -- if you want to distinguish multiple values
    )[OFFSET(0)].*,
FROM ~
Jiho Choi
  • 1,083
  • 1
  • 10
  • 26