CODEWITHSUNDEEP
sqlsql-server
CodeSniper
CodeSniper

Reputation: 493

Calculate sum in SQL and display it as another column?

I used the following query for calculating credit of customers but the problem i want to calculate the sum of CREDIT column in another which just sum up the CREDIT and display in another column .Is there any way of doing this by modifying following query? Any help would be highly appreciable. 

Select
    CUSTOMER_NAME
   ,TODAYS_TOTAL
   ,AMOUNT_RECIEVED
   ,DATE_SALE
   ,ITEM_MODEL
   ,QUANTITY
   ,CREDIT = (CASE 
      WHEN AMOUNT_RECIEVED=0 AND TODAYS_TOTAL>0 AND CREDIT =0 THEN TODAYS_TOTAL 
      WHEN AMOUNT_RECIEVED>0 AND TODAYS_TOTAL>0 AND CREDIT>=0 THEN (CREDIT+TODAYS_TOTAL)-AMOUNT_RECIEVED
      WHEN AMOUNT_RECIEVED>0 AND TODAYS_TOTAL=0 AND CREDIT >0 THEN (CREDIT-AMOUNT_RECIEVED)
      END) 
From CUSTOMER_CREDIT_RECORDS 
WHERE CUSTOMER_NAME='Saad NED'

Upvotes: 0

Views: 108

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

First, here is your query more understandably formatted:

Select CUSTOMER_NAME, TODAYS_TOTAL, AMOUNT_RECIEVED, DATE_SALE, ITEM_MODEL, QUANTITY,
       CREDIT = (CASE WHEN AMOUNT_RECIEVED=0 AND TODAYS_TOTAL>0 AND CREDIT=0
                      THEN TODAYS_TOTAL 
                      WHEN AMOUNT_RECIEVED>0 AND TODAYS_TOTAL>0 AND CREDIT>=0
                      THEN (CREDIT+TODAYS_TOTAL)-AMOUNT_RECIEVED
                      WHEN AMOUNT_RECIEVED>0 AND TODAYS_TOTAL=0 AND CREDIT>0
                      THEN (CREDIT-AMOUNT_RECIEVED)
                 END) 
From CUSTOMER_CREDIT_RECORDS
WHERE CUSTOMER_NAME = 'Saad NED';

You only have one customer, so most of the columns don't make sense for a daily total. You can do:

Select CUSTOMER_NAME, 
       SUM(CASE WHEN AMOUNT_RECIEVED=0 AND TODAYS_TOTAL>0 AND CREDIT=0
                THEN TODAYS_TOTAL 
                WHEN AMOUNT_RECIEVED>0 AND TODAYS_TOTAL>0 AND CREDIT>=0
                THEN (CREDIT+TODAYS_TOTAL)-AMOUNT_RECIEVED
                WHEN AMOUNT_RECIEVED>0 AND TODAYS_TOTAL=0 AND CREDIT>0
                THEN (CREDIT-AMOUNT_RECIEVED)
           END) as CREDIT
From CUSTOMER_CREDIT_RECORDS
WHERE CUSTOMER_NAME = 'Saad NED'
GROUP BY CUSTOMER_NAME;

However, I'm mostly curious about the arithmetic in the case statements. Do you have an aversion to adding values that are 0? It seems much simpler to do:

Select CUSTOMER_NAME, 
       SUM(CREDIT + TODAYS_TOTAL - AMOUNT_RECIEVED) as CREDIT
From CUSTOMER_CREDIT_RECORDS
WHERE CUSTOMER_NAME = 'Saad NED'
GROUP BY CUSTOMER_NAME;

This assumes that at least one of the when conditions is satisfied.

Upvotes: 2

Related Questions