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';
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.
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.
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.
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.
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';
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With