A SQL Tidbit: Conditional Aggregates

Thanks, Sean!
For some reason I thought Oracle had IFfunction like MySQL:
http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_if

I figured showing the CASE would be more universal, but was trying to show alternatives. I like DECODE and SWITCH (MS Access) also and consider showing those as examples, so glad you brought that up here for readers.

Thanks again for the confirmation on not being able to use IF inline with Oracle.

Regards,
Kevin

Thanks again, Sean. I updated article to reflect more correctly IF in MySQL would look similar to MS Access IIF.

For those who embrace the use of IF/THEN with no ELSE (utilizing aggregate treatment of NULLs), note that this also works while we are on the topic of IIF:

select count( iif(isMale = 0, 1) ) from SQLExperts;
                                      

1:

Additionally, I added two more examples of DECODE and SWITCH since they are a bit different as syntax.

decode(expression, search, result [,search, result]...[,default] )
                                      

1:

switch(expression1, value1, expression2, value2, ... expression_n, value_n )
                                      

1:

At least for SWITCH() for the default case, you can use a final expression of the boolean value TRUE like so:

switch(isMale = 0, 1, true, 0)
                                      

1:

Since in our cases NULL or no default case is fine, we may not need an alternative to this in DECODE; however, as a side note, Sean is the preferred method simply to combine this with COALESCE?

coalesce(decode(isMale, 0, 'Female'), 'Male')
                                      

1:

Anyway, thanks all for reading and hopefully this is now more accurate and my apologies to those I may have had chasing around looking for an IF function in Oracle. *smile*

Regards,

Kevin

no problem, and thank you for the correction on MySQL.  It's obviously not my strong suit (yet) I'll take all the pointers I can get there.


Another interesting conditional function in Oracle is LNNVL.  
It allows comparisons with NULL's (which DECODE also allows)
 Here's an example I use in my IOUG presentation

The last 2 columns returned are just for academic interest.  LNNVL returns a boolean which isn't normally allowed in Oracle SQL, but is here but is treated as a NULL if
returned directly instead of being nested within another condition.

SELECT a,
       b,
       CASE WHEN LNNVL(a = b) THEN 1 ELSE 0 END "lnnvl(a=b)",
       CASE WHEN LNNVL(a != b) THEN 1 ELSE 0 END "lnnvl(a!=b)",
       LNNVL(a = b),
       LNNVL(a != b)
  FROM (SELECT TO_NUMBER(NULL) a, TO_NUMBER(NULL) b FROM DUAL
        UNION ALL
        SELECT TO_NUMBER(NULL) a, 1 b FROM DUAL
        UNION ALL
        SELECT 1 a, TO_NUMBER(NULL) b FROM DUAL
        UNION ALL
        SELECT 1 a, 1 b FROM DUAL
        UNION ALL
        SELECT 1 a, 2 b FROM DUAL);

That is pretty cool and good to add to the toolkit. Always nice having conversations with folks like yourself. If I haven't mentioned it lately, I love Experts Exchange!

And never mind my question at http:#c17826 as I just remembered (*sigh* even noted it above) that decode has in its syntax a default result.

So for those following along, you can just do:

decode(isMale, 0, 'Female', 'Male')
                                      

1:

or

select sum( decode(isMale, 0, 1, 0) ) from SQLExperts;
                                      

1:

oops, looks like we were simul-posting.

as for your last question on coalesce(decode(isMale, 0, 'Female'), 'Male')

I would simply include the "else" or "default" in the decode rather than forcing a NULL and then using COALESCE or NVL to convert it to Male


select isMale,coalesce(decode(isMale, 0, 'Female'), 'Male') will_work, decode(isMale,0,'Female','Male') preferred
from (select 0 isMale from dual union all select 1 isMale from dual union all select null isMale from dual)

oops again.  yes, you found it...  :)

Thanks!

On an inverse or complimentary topic, since we have been talking about filtering data during aggregation, it is nice to note that if you want to include NULLs rows some of the tools mentioned by Sean can come in very handy.

Consider taking an average of sales over 5 years. If the NULL rows are year 1 and year 2, it seems almost intuitive that AVG() function answer for sales of 5, 10 and 15 is correct at 10.

select avg(sales)
from (
   select 1 as yr, cast(null as int) as sales
   union select 2, null
   union select 3, 5
   union select 4, 10
   union select 5, 15
) t
                                      

1:
2:
3:
4:
5:
6:
7:
8:

Consider now though how you would want to treat NULL rows in year 3 and year 4 with sales in year 1, 2 and 5. Some businesses may want to get a true 5 year average treating the nulls as 0.

That is nicely acheived using NVL, NZ, COALESCE or other such functions shorthanding the syntax of CASE WHEN some_value IS NULL THEN ... END.

See how this change alters the above average:

avg( coalesce(sales, 0) )
                                      

1:

 

Kevin

and it is a yes from me too :)

select count(*) from SQLExperts where ismale=0

@DineshKammath:

Thanks for the comment, but I am failing to see its point? That query was presented in the article content above ... what did you mean to say here or did you mean to post elsewhere?

Kevin

A nice treatment of the Switch function recently published:
Using the Switch Function in Microsoft Access

Thought it might interest those here since we touched briefly on Switch.

Kevin

Addendum: Conditional Aggregate PIVOTing In Action

select ID
     , max(case [key] when 'name' then value end) as [Name]
     , max(case [key] when 'surname' then value end) as [Surname]
     , max(case [key] when 'identity' then value end) as [Identity]
from your_table_name
group by id
;
                                      

1:
2:
3:
4:
5:
6:
7:

I thought this was a good example to share as I focused in a lot on SUM() and COUNT() in the article as that is typically where we see conditionals during aggregation. MAX() and other aggregates make sense too at times, so just good to see others in action. In this scenario, we are pivoting data of probably the VarChar variety as it would need to be something along those lines to store both numeric and string data. Since you wouldn't typically SUM() string data, MAX() comes to mind but is especially appropriate since the expectation here is that there is only one instance of each key per ID; therefore, keep in mind this could easily also be shown with MIN().

Thanks again for reading!

Regards,
Kevin

Addendum: Be Fearful of Nothing

select sum(case when primaryZone like 'MSSQL%' then 1 else 0 end)
from SQLExperts;
                                      

1:
2:

select sum(case when primaryZone like 'MSSQL%' then 1 else null end)
from SQLExperts;
                                      

1:
2:

select sum(case when primaryZone like 'MSSQL%' then 1 end)
from SQLExperts;
                                      

1:
2:

A point that came up recently, that is good also to note here, is that this is not always true.  In most cases when doing conditional aggregates, the basis of the approach is that you expect at least one row to meet your criteria or are using COUNT() to tally the number of rows that do.  COUNT() will return 0 if all row data counted is NULL; however, just be mindful that other aggregate functions, like SUM() or AVG(), may return NULL in the same scenario.

So to refine the above, those three statements have the same results as long as you have at least one row meeting the conditional in the aggregate.  Otherwise, you will get 0 for the first and NULL for the other two.

Subsequently, this subtle difference is typically fine and often desired when dealing with pivoting data, at least in my experience, as it is often helpful for us to see where we had no sales or other activity (i.e., NULL sum) versus a net 0 month (i.e., sales but returns or credits that offset those sales).  However, I saw a case recently where the conditional sum was used later in a math equation and NULL + 5 = NULL when using the standard arithmetic operators which is unlike the SUM() of two rows with data of 5 and NULL, respectively.


Hopefully that was clear and a useful addition to our tidbit on conditional aggregates.

Thanks again for reading and voting,

Kevin

Kevin,

Great article!  

A few suggestions:

1. In general, it is a good practice to avoid embedding VBA functions in your Access queries, and it usually can be done.  They slow things down (Domain Aggregate functions are the most notorious for this).

I haven't actually benchmarked  this query using iif (which I don't think would have nearly the same performance hit as a domain aggregate function):

select sum(iif(isMale=0, 1, 0)) * 100.0 / count(*)
from SQLExperts;
                                      

1:
2:

But it can be written equivalently without embedded VBA as:

SELECT Sum(-1 * Not IsMale)*100/Count(*) AS [Female Experts]
FROM SQLExperts;
                                      

1:
2:

2. "NULLIF function if available on your SQL platform to generate nulls ..."
Do you want to include an alternative for other platforms as a sidenote?

select sum(-1 * (primaryZone <> 'MSACCESS')) as cnt
from SQLExperts;
                                      

1:
2:

3.  Under the Pivot section, this is an option for systems (such as Access) that don't support CASE:

select PrimaryZone
     , Sum(-1 * Not [IsMale]) as Female
     , Sum(-1 * [IsMale]) as Male
from SQLExperts
group by primaryZone;
 
                                      

1:
2:
3:
4:
5:
6: