As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time!
----------------+-------+------------
expert |isMale |primaryZone
----------------+-------+------------
acperkins | 1 | MSSQL2K5
aneeshattingal | 1 | MSSQL2K5
angeliii | 1 | MSSQLSVR
Helen_Feddema | 0 | MSACCESS
matthewspatrick | 1 | SQLSYNTX
mbizup | 0 | MSACCESS
mwvisa1 | 1 | SQLSYNTX
ScottPletcher | 1 | MSSQLSVR
sdstuber | 1 | ORACLEDB
slightwv | 1 | ORACLEDB
----------------+-------+------------
create table SQLExperts(
expert varchar(50) primary key,
isMale bit,
primaryZone char(8)
);
insert into SQLExperts(expert, isMale, primaryZone)
select 'mbizup', 0, 'MSACCESS' union
select 'Helen_Feddema', 0, 'MSACCESS' union
select 'matthewspatrick', 1, 'SQLSYNTX' union
select 'mwvisa1', 1, 'SQLSYNTX' union
select 'angeliii', 1, 'MSSQLSVR' union
select 'ScottPletcher', 1, 'MSSQLSVR' union
select 'acperkins', 1, 'MSSQL2K5' union
select 'aneeshattingal', 1, 'MSSQL2K5' union
select 'sdstuber', 1, 'ORACLEDB' union
select 'slightwv', 1, 'ORACLEDB'
;
select count(*) as cnt from SQLExperts;
select count(*) as cnt
from SQLExperts
where isMale = 0;
This works, but what is the percent of female Experts to the total?
select (select count(*) from SQLExperts where isMale = 0) * 100.0 / count(*) as femaleExperts
from SQLExperts;
select count(case isMale when 0 then expert end) * 100.0 / count(*) as femaleExperts
from SQLExperts;
select sum(iif(isMale=0, 1, 0)) * 100.0 / count(*) as femaleExperts
from SQLExperts;
select count(switch(isMale = 0, 1)) * 100.0 / count(*) as femaleExperts
from SQLExperts;
select sum(decode(isMale, 0, 1)) * 100.0 / count(*) as femaleExperts
from SQLExperts;
select sum(case when primaryZone like 'MSSQL%' and isMale = 1 then 1 else 0 end) as cnt
from SQLExperts;
select sum(case when primaryZone like 'MSSQL%' and isMale = 1 then 1 else null end) as cnt
from SQLExperts;
select sum(case when primaryZone like 'MSSQL%' and isMale = 1 then 1 end) as cnt
from SQLExperts;
select count(nullif(isMale, 0)) as cnt
from SQLExperts;
select count(nullif(primaryZone, 'MSACCESS')) as cnt
from SQLExperts;
select PrimaryZone
, count(case isMale when 0 then expert end) as Female
, count(case isMale when 1 then expert end) as Male
from SQLExperts
group by primaryZone;
select primaryZone, [0] as Female, [1] as Male
from SQLExperts
pivot (count(expert) for isMale in ([0],[1])) pvt;
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (17)
Author
Commented:Using the Switch Function in Microsoft Access
Thought it might interest those here since we touched briefly on Switch.
Kevin
Author
Commented:Addendum: Conditional Aggregate PIVOTing In Action
In a recent question, MattDuPlessis, presented a good scenario for demonstrating cross tabulation using conditional aggregation in the question Group and Transpose data.The Request
[step=""]Table 1.1: Original Data
Open in new window
Table 1.2: Desired Results
Open in new window
[/step]The Conditional Aggregate Solution
Open in new window
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
Author
Commented:Addendum: Be Fearful of Nothing
In section #3 (NULLIF), we discussed how the following were equivalent in their results.
Open in new window
Open in new window
Open in new window
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
Commented:
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):
Open in new window
But it can be written equivalently without embedded VBA as:
Open in new window
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?
Open in new window
3. Under the Pivot section, this is an option for systems (such as Access) that don't support CASE:
Open in new window
Author
Commented:View More