CODEWITHSUNDEEP
sqljoinhiveunion
user175084
user175084

Reputation: 4630

Write a custom join query in hive

I have two tables

table J:
P_ID  event   T_ID              URL
8187    6   14690481058450526   maplestage.com
8187    6   14690481058450527   maplestage.com

Table A:
P_ID  event   T_ID              URL
8187    7   14690481058450526   NULL
8187    7   14690481058450526   NULL
8187    7   14690481058450527   NULL

I have the following query to count the event that are 6 and events that are 7:

SELECT sum(if(j.event=6,1,0)) Type1, j.P_ID, j.URL, sum(if(a.event=7,1,0)) Type2  FROM Tabel_J j LEFT outer join Table_A a on a.T_ID = j.T_ID GROUP BY j.P_ID, j.URL;

The result i am getting is this:

Type1   P_ID          URL         Type2
3       8187      maplestage.com    3

The result i want to get is:

Type1   P_ID          URL         Type2
2       8187      maplestage.com    3

Please can someone help me with this.

Thanks

Upvotes: 0

Views: 74

Answers (1)

rchang
rchang

Reputation: 5236

From your data, the significance of the T_ID column isn't immediately obvious to me. Having said that, based on what I think you are after (perhaps I'm wrong), this query might get you there:

SELECT
  SUM(IF(event=6,1,0)) Type1,
  P_ID,
  collect_set(URL)[0] URL,
  SUM(IF(event=7,1,0)) Type2
FROM
  (SELECT * FROM tabel_j UNION ALL SELECT * FROM table_a) everything
GROUP BY P_ID;

Upvotes: 1

Related Questions