CODEWITHSUNDEEP
sqlhive
Caerus
Caerus

Reputation: 674

How to correct error when aggregating from subquery

I have a query that looks like this:

SELECT store_id,
(CASE WHEN txns_A>0 AND txns_B=0 THEN 'A Only' WHEN txns_A=0 AND txns_B>0 THEN 'B Only' END) A_B_indicator,
sum(1) cnt_customers,
sum(spend_A+spend_B)/sum(txns_A+txns_B) avg_receipt
FROM(
    SELECT store_id, cust_id
    SUM(CASE WHEN A_B_indicator='A' THEN spend else 0 end) spend_A,
    SUM(CASE WHEN A_B_indicator='B' THEN spend else 0 end) spend_B,
    SUM(CASE WHEN A_B_indicator='A' THEN spend else 0 end) txns_A,
    SUM(CASE WHEN A_B_indicator='B' THEN spend else 0 end) txns_B
    FROM table1
    GROUP BY store_id, cust_id
    ) table2;

However, this generates an error because store_id is not in a GROUP BY clause. When I rewrite the query to include a GROUP BY store_id clause, it complains that the aggregate columns are not in the Group By. However, if I add them by rewriting the Group By to be Group BY 1,2,3,4, this also generates an error (Not yet supported place for UDAF Sum).

How can I rewrite this query to be error-free?

Upvotes: 0

Views: 29

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You can write this as:

SELECT store_id,
        (CASE WHEN SUM(txns_A) > 0 AND SUM(txns_B) = 0 THEN 'A Only'
              WHEN SUM(txns_A) = 0 AND SUM(txns_B) > 0 THEN 'B Only'
         END) as A_B_indicator,
       COUNT(*) as cnt_customers,
       SUM(spend_A+spend_B)/sum(txns_A+txns_B) as avg_receipt
FROM (SELECT store_id, cust_id
             SUM(CASE WHEN A_B_indicator='A' THEN spend else 0 end) as spend_A,
             SUM(CASE WHEN A_B_indicator='B' THEN spend else 0 end) as spend_B,
             SUM(CASE WHEN A_B_indicator='A' THEN spend else 0 end) as txns_A,
             SUM(CASE WHEN A_B_indicator='B' THEN spend else 0 end) as txns_B
      FROM table1
      GROUP BY store_id, cust_id
    ) table2
GROUP BY store_id;

Upvotes: 1

Related Questions