Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SELECT query with CASE condition and SUM()

I'm currently using these sql statements. My table has the field CPaymentType which contains "Cash" or "Check". I can sum up the amount of payments by executing 2 SQL statements as shown below. In this case, the user won't even notice the speed difference when executing 2 sql statements or just 1, however, I don't like my way, I just want 1 sql statement. How do I reconstruct these into 1 statement with CASE conditions? I can't figure it out since examples online result in either 1 or 0 or boolean. I don't want the postdated Check payments to be included. Thank you very much.

Select SUM(CAmount) as PaymentAmount  from TableOrderPayment  where CPaymentType='Cash' and CStatus='Active';  Select SUM(CAmount) as PaymentAmount  from TableOrderPayment  where CPaymentType='Check' and CDate<=SYSDATETIME() and CStatus='Active'; 
like image 922
chris_techno25 Avatar asked Jan 05 '14 15:01

chris_techno25


People also ask

How do I Sumif in SQL query?

The SUMIF function in Excel returns the sum of cells that meet a single condition. In the condition you can use logical operator (<,>,<>,=) and wildcards (*,$) for partial conditions. Range = range of cells where you want to check the criteria. Criteria= the condition.

Can we use aggregate function in case statement?

CASE statement in SQL and aggregate functions Aggregate functions in SQL Server perform calculations and return a single value. Examples of aggregate functions are MIN, MAX, COUNT, ABG and CHECKSUM. For this purpose, we use the COUNT aggregate function in SQL Server.

How do I SUM in SELECT query?

If you need to add a group of numbers in your table you can use the SUM function in SQL. This is the basic syntax: SELECT SUM(column_name) FROM table_name; The SELECT statement in SQL tells the computer to get data from the table.

Can we use case in SELECT statement?

CASE can be used in any statement or clause that allows a valid expression. For example, you can use CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select_list, IN, WHERE, ORDER BY, and HAVING.


1 Answers

Select SUM(CASE When CPayment='Cash' Then CAmount Else 0 End ) as CashPaymentAmount,        SUM(CASE When CPayment='Check' Then CAmount Else 0 End ) as CheckPaymentAmount from TableOrderPayment Where ( CPayment='Cash' Or CPayment='Check' ) AND CDate<=SYSDATETIME() and CStatus='Active'; 
like image 138
Mudassir Hasan Avatar answered Sep 27 '22 22:09

Mudassir Hasan