Multiple Aggregate functions with a group by clause

Question

I have the following

WorkflowID  FK_UA                DateApprobation
----------- -------------------- -----------------------
1           3                    NULL
2           1                    NULL
3           1                    NULL
4           2                    2013-05-31 09:22:33.000

What I'm looking to do is to get a bunch of aggregate fields.

I want to get Approbated workflow , Non-Approbated workflow, All Workflows

The way I'm knowing that is if the "DateApprobation" field is null or has a value.

The thing is, I want to be able to group that by "FK_UA" so I don't know how to have 3 aggregate functions (COUNT) with a group by clause.

I'm looking for a query that can achieve that, I've tried a couple of similar case i found and it returned some weird values.

I tried this :

SELECT
    FK_UA
,COUNT(WorkflowID) AS TOTAL
,COUNT(CASE when DateApprobation is not null then 1 else 0 end) AS APPROVED
,COUNT(CASE when DateApprobation is null then 1 else 0 end) AS NOT_APPROVED   
FROM Workflow        
GROUP BY
FK_UA

but it always return the same things for all 3 values!

Answer 1

SELECT
    SUM(CASE WHEN [DateApprobation] IS NOT NULL THEN 1 ELSE 0 END) as [Approbated count],
    SUM(CASE WHEN [DateApprobation] IS NULL THEN 1 ELSE 0 END) as [Non-Approbated count],
    COUNT(*) as [Total]
FROM YourTable
GROUP BY FK_UA 

If I got you right....

Answer 2

A standard SQL solution using COUNT()

You could have also used COUNT() but make sure you turn the values you don't want to count into NULL, not 0, as aggregate functions do not aggregate NULL values in SQL

SELECT
  fk_ua,
  COUNT(WorkflowID) AS total,
  COUNT(CASE WHEN DateApprobation IS NOT NULL THEN 1 END) AS approved,
  COUNT(CASE WHEN DateApprobation IS NULL THEN 1 END) AS not_approved
FROM Workflow        
GROUP BY fk_ua

In fact, you could take this one step further in your case, because you're already counting the NOT NULL values:

SELECT
  fk_ua,
  COUNT(WorkflowID) AS total,
  COUNT(DateApprobation) AS approved,
  COUNT(WorkflowID) - COUNT(DateApprobation) AS not_approved
FROM Workflow        
GROUP BY fk_ua

Or alternatively:

SELECT fk_ua, total, approved, total - approved AS not_approved
FROM (
  SELECT
    fk_ua,
    COUNT(WorkflowID) AS total,
    COUNT(DateApprobation) AS approved
  FROM Workflow
  GROUP BY fk_ua
) t

For large data sets, this might be slightly faster as your database should be able to recognise that there are only 2 distinct COUNT(...) expressions. Most commercial databases do.

A standard SQL solution using FILTER

Some SQL dialects, including e.g. PostgreSQL, implement the standard FILTER clause, which you can use to make things a bit more readable. Your query would then read:

SELECT
  fk_ua,
  COUNT(*) AS total,
  COUNT(*) FILTER (WHERE DateApprobation IS NOT NULL) AS approved,
  COUNT(*) FILTER (WHERE DateApprobation IS NULL) AS not_approved
FROM Workflow        
GROUP BY fk_ua