Hot answers tagged

5

Since correlated subqueries are not allowed, try using derived tables and then joining them. select a.id, a.sum_all, sum(b.sum_all) as csum from ( select col1 as id, sum(col2) as sum_all from t group by col1 ) a join ( select col1 as id, sum(col2) as sum_all ...


3

I suspect you are using a Hive version that is before 0.13.0. In that case the problem is the AS keyword when giving alias to subquery. From HiveQL documentation Subqueries in the FROM Clause: SELECT ... FROM (subquery) name ... SELECT ... FROM (subquery) AS name ... (Note: Only valid starting with Hive 0.13.0) ... The optional keyword "AS" can ...


2

The syntax of you query is not often called "correlated subquery". A correlated subquery would be like this: SELECT ndo.sku AS ParentSKU, ( SELECT SUM(g.sessions) FROM gasessiondata AS g WHERE g.date >= ndo.FromDate AND g.date <= ndo.ToDate ) AS s FROM Temp.NumberOfDaysOnline AS ndo ; Your query ...


2

I'm not too sure what you are doing there and that's not at all how I write queries. Either way, I would try it this way: select a.MBR_ID_TYP_ID || '-' || b.ref_desc as "Member Type" , a.* from mbr_id as a left outer join ref as b on b.ref_nm = 'memberIDType' and a.REF_CD = b.MBR_ID_TYP_ID -- or: b.REF_CD = a.MBR_ID_TYP_ID ...


2

Welcome. Here is what you want. But please follow the notes given below too. SELECT day, ip_address, user_name, timestamp, count(ip_address) AS tct FROM my_table WHERE day = current_date() GROUP BY ip_address HAVING tct> 10 ORDER BY ip_address ASC Do not use select * instead give specific column names. You are speaking of ...


2

All this approach does in to make your original query a derived table then group by pub_id. SET hive.auto.convert.join = TRUE; SELECT max(DEAL_ID) as DEAL_ID , EXCHANGE_ID , PUB_ID , sum(INCREMENTAL) as INCREMENTAL , sum(FIRST_IMPS) as FIRST_IMPS , sum(SECOND_IMPS) as SECOND_IMPS FROM ( SELECT First.deal_id , ...


1

As long as one ip address has more than 20 rows, all these rows will be returned. If less then 20, do not return. WITH cte AS ( SELECT *, COUNT(ip_address) OVER (PARTITION BY ip_address) cnt FROM my_table WHERE DAY = current_date() ) SELECT * FROM cte WHERE cnt > 20;


1

I've found that this solution works. It's not very elegant and I'm worried over scale(does it run the subquery twice or once) but it works. Here is the fiddle WITH subquery as ( SELECT First.deal_id ,COALESCE( First.exchange, Second.exchange ) as exchange_id ,COALESCE( First.publisher, Second.publisher ) as pub_id ,COUNT (DISTINCT(...


Only top voted, non community-wiki answers of a minimum length are eligible