Select Statement is not giving desired result.
I have created a table in Hive:
CREATE TABLE user (u_id INT, series_id INT, n_id INT, side STRING) ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE;
Inserted 6 rows into that table. and after that i got my table like........
hive> SELECT * FROM user;
OK
26 210 301223 "FRONT"
26 210 301224 "BACK"
90113 210 301223 "BACK"
90113 210 301224 "FRONT"
323694 210 301223 "FRONT"
323694 210 301224 "FRONT"
But given below query is not giving right data.
hive> SELECT a1.series_id AS sid, a1.n_id AS nid, a2.series_id AS asid, a2.n_id AS anid,
SUM(IF(a1.side="FRONT" AND a2.side="FRONT", 1, 0)) AS xx,
SUM(IF(a1.side="FRONT" AND a2.side="BACK", 1, 0)) AS yy,
SUM(IF(a1.side="BACK" AND a2.side="FRONT", 1, 0)) AS zz,
SUM(IF(a1.side="BACK" AND a2.side="BACK", 1, 0)) AS ww
FROM user a1
JOIN user a2 ON a1.u_id=a2.u_id
GROUP BY a1.series_id, a1.n_id, a2.series_id, a2.n_id;
................
Status: Finished successfully
OK
210 301223 210 301223 0 0 0 0
210 301223 210 301224 0 0 0 0
210 301224 210 301223 0 0 0 0
210 301224 210 301224 0 0 0 0
Time taken: 18.17 seconds, Fetched: 4 row(s)
Please suggest.
Thanks in advance