Link to home
Start Free TrialLog in
Avatar of Murray Brown
Murray BrownFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Google BigQuery UNION two SQL statements

Hi. In Google BigQuery how do I UNION the following two SQL statements. I tried standard SQL and it didn't work


SELECT PERIOD_END_DATE,

DIVISION,

SHOPPING_CHANNEL,

RETAILER,

CUSTOMER_FREE_COLUMN_1,

POS_UNIVERSAL_CODE,

EAN_UPC_CODE,

Sum(SOLD_AMOUNT_IN_LOCAL_CURRENCY) as sellout,

Sum(STOCK_IN_POS_IN_UNITS) as stock_units,

Sum(SOLD_QUANTITY_IN_UNITS) as units,

Sum(custom_kpi_10) as stock,

Sum(stock_in_pos_in_local_currency) as stock_value

FROM `emea-datafoundat-gbl-emea-pd.d_sellout_warehouse_eu_pd.t_warehouse_sellout_v1` 

WHERE YEAR = CAST(FORMAT_DATE('%Y',CURRENT_DATE()) AS INT64)

--AND UPPER(DATA_FILE_PROVIDER) LIKE "%PYTHON%"

AND DIVISION LIKE '%ACD%'

AND loreal_market_flag LIKE '%L%'

AND (country_code LIKE '%UK%' OR COUNTRY_CODE LIKE '%IE%')

GROUP BY PERIOD_END_DATE, DIVISION, SHOPPING_CHANNEL, RETAILER, CUSTOMER_FREE_COLUMN_1, POS_UNIVERSAL_CODE, EAN_UPC_CODE

 

UNION

 

SELECT PERIOD_END_DATE as period_end_date,

'ACD' as division,

CHANNEL As SHOPPING_CHANNEL,

RETAILER,

CUSTOMER_FREE_COLUMN_1 As CUSTOMER_FREE_COLUMN_1,

'' as POS_UNIVERSAL_CODE,

EAN_CODE As EAN_UPC_CODE,

Sum(VALUE) as sellout,

'' As stock_units,

Sum(UNITS) as units,

'' as stock,

'' as stock_value

FROM `emea-sales-gbl-emea-pd.sellout_bqdset_eu_pd.New_ACD_WRS`

WHERE YEAR = CAST(FORMAT_DATE('%Y',CURRENT_DATE()) AS INT64)

GROUP BY PERIOD_END_DATE, DIVISION, SHOPPING_CHANNEL, RETAILER, CUSTOMER_FREE_COLUMN_1, POS_UNIVERSAL_CODE, EAN_UPC_CODE

ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

>>I tried standard SQL and it didn't work

Define "didn't work".
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial