SQL Summing columns based on date key

user_8908611 0
Viewed 50

I have a dataset as given in the link, DataSet

I want to segregate the column "order_item_unit_status" as separate column and bring respective transaction amount for the same. Desired output is given below.

enter image description here

Objective is to consolidate the txn_amt into respective categories and group them based on txn_date_key. (Basically pivoting based on order_item_unit_status column and bringing txn_amt respectively.)

I used the below code,

Select *, CASE WHEN order_item_unit_status ='DELIVERED'
THEN txn_amt ELSE 0 END as DELIVERED, 
CASE WHEN order_item_unit_status ='RETURNED'
THEN txn_amt ELSE 0 END as RETURNED
from sales

Got output as referred in the link Output

The output is not grouping based on txn_date_key and multiple line items found. If i use GROUP BY txn_date_key an error is thrown.

Also I was informed that server is supported by HiveSQL and does not support of using ":", date time, and temp tables can not be created. I'm currently stuck on how to go about given the constraints.

Help would be much appreciated

hiveql hive sql
2 Answers

You have to use your columns in the group by:

EDIT: also SUM() added for the correct output...

Select *, 

SUM(CASE WHEN order_item_unit_status ='DELIVERED'
THEN txn_amt ELSE 0 END) as DELIVERED, 

SUM(CASE WHEN order_item_unit_status ='RETURNED'
THEN txn_amt ELSE 0 END) as RETURNED

from sales
group by txn_amt,txn_date_key,order_item_unit_status

In hivesql you can use from_unixtime command

Unixtime

user_104578770

All columns which are not aggregated and selected shold be in group by.

This query produces result you need:

Select txn_date_key, 
       sum(CASE WHEN order_item_unit_status ='DELIVERED' 
                THEN txn_amt ELSE 0 END)                  as DELIVERED, 
       sum(CASE WHEN order_item_unit_status ='RETURNED'
                THEN txn_amt ELSE 0 END)                  as RETURNED
from sales
group by txn_date_key

Result:

txn_date_key    delivered   returned
20190701           3200     0
20210631           0        3000
user_27003440
Related