Menu
I have a table in this format
COL1 COL2 COL3
A NULL 4
A NULL 4
A VAL1 4
A VAL2 4
A VAL3 4
B NULL 5
B VAL1 6
I need to bring out the output as follows:
COL1 TOTAL VALID
A 20 12
B 11 6
My Question is, how do I get the 'VALID' column - it should do sum of col3 only if col2 is not null.
I was able to get the 'TOTAL' field correctly using SUM and group by clauses. But how do I compute the 'VALID' column?
Any ideas? Thanks a lot!
530
A method to simplify this query and to run within a single select that I have used with success is to use a “conditional” sum in the SQL query. So rather than running COUNT(*) we will be running SUM(…) . We will combine the SUM() call with a CASE statement, so that it counts correctly.
A CASE WHEN expression is often used with a SUM() function in more complex reports, which can be quite challenging for beginners. Even though you're probably used to using the SUM() function for summing values, it can also be used for counting. This example will help you understand the concept better.
The SQL Server SUM() function is an aggregate function that calculates the sum of all or distinct values in an expression. In this syntax: ALL instructs the SUM() function to return the sum of all values including duplicates. ALL is used by default.
Conditional statements are used to define what logic is to be executed based on the status of some condition being satisfied. There are two types of conditional statements supported in SQL procedures: CASE.
Something like this:
SELECT col1
, SUM(col3) AS TOTAL
, SUM(CASE WHEN col2 IS NOT NULL THEN col3 ELSE NULL END) AS VALID
FROM mytable
GROUP BY col1
The "trick" is to use the CASE expression to conditionally return the value of col3.
101
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
