CODEWITHSUNDEEP
sql-server-2008group-bysql-updatesumduplicates
user3139717
user3139717

Reputation: 3

SQL update within same table based on summation

I have this table called t, as shown below.

   cust_id|    brand|    bill_amt|       AA|        BB|
   1234   |       AA|         100|     NULL|      NULL|
   5678   |       AA|         300|     NULL|      NULL|
   5678   |       BB|         350|     NULL|      NULL|
   5678   |       BB|         600|     NULL|      NULL|

I have around hundred thousands of records in my data, is there anyway I can update t table as shown below?

   cust_id|    brand|    bill_amt|       AA|        BB|
   1234   |       AA|         100|      100|      NULL|
   5678   |       AA|         300|      300|       950|
   5678   |       BB|         350|      300|       950|
   5678   |       BB|         600|      300|       950|

How can I show the bill_amt values under the AA and BB columns?
How can I sum up the bill_amt spent on the brand within the same customer?

For example, looking at customer 5678, she spent $350 and $600 on different days for brand BB, how can I show in BB Column the summation of the bill_amt she spent for BB?
Also, the same customer has spent $300 on AA, how can I show this value in the AA column?

Upvotes: 0

Views: 120

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 180947

To select the summary without actually creating a summary field in the table, you can simply use OVER; creating this as a view would most likely allow you the same advantages without altering the table.

SELECT CUST_ID, BRAND, BILL_AMT, 
  SUM(CASE WHEN brand='AA' THEN bill_amt ELSE NULL END) 
    OVER (PARTITION BY cust_id) AA,
  SUM(CASE WHEN brand='BB' THEN bill_amt ELSE NULL END) 
    OVER (PARTITION BY cust_id) BB
FROM t;

To actually update the table, you can use a common table expression, however I'd sincerely advise against this except possibly for historical data that can never change, as changes to the data will no longer automatically update the summary.

WITH cte AS (
  SELECT *,
    SUM(CASE WHEN brand='AA' THEN bill_amt ELSE NULL END) 
      OVER (PARTITION BY cust_id) AA2,
    SUM(CASE WHEN brand='BB' THEN bill_amt ELSE NULL END) 
      OVER (PARTITION BY cust_id) BB2
  FROM t
) 
UPDATE cte SET AA=AA2, BB=BB2;

An SQLfiddle for testing both ways.

Upvotes: 3

Related Questions