How can I use PIVOT to show simultationly average and count in its cells?

Question

Looking at the syntax I get the strong impression, that PIVOT doesn't support anything beyond a single aggregate function to be calculated for a cell.

From statistical view showing just some averages without giving the number of cases an average refers to is very unsatisfying ( that is the polite version ).

Is there some nice pattern to evaluate pivots based on avg and pivots based on count and mix them together to give a nice result?

Answer 1

Yes you need to use the old style cross tab for this. The PIVOT is just syntactic sugar that resolves to pretty much the same approach.

SELECT AVG(CASE WHEN col='foo' THEN col END) AS AvgFoo,
       COUNT(CASE WHEN col='foo' THEN col END) AS CountFoo,...

If you have many aggregates you could always use a CTE

WITH cte As
(
SELECT CASE WHEN col='foo' THEN col END AS Foo...
)
SELECT MAX(Foo),MIN(Foo), COUNT(Foo), STDEV(Foo)
FROM cte

Answer 2

Simultaneous.. in its cells. So you mean within the same cell, therefore as a varchar?

You could calc the avg and count values in an aggregate query before using the pivot, and concatenate them together as text.

The role of the PIVOT operator here would only be to transform rows to columns, and some aggregate function (e.g. MAX/MIN) would be used only because it is required by the syntax - your pre-calculated aggregate query would only have one value per pivoted column.

EDIT

Following bernd_k's oracle/mssql solution, I would like to point out another way to do this in SQL Server. It requires streamlining the multiple columns into a single column.

SELECT MODULE,
  modus + '_' + case which when 1 then 'AVG' else 'COUNT' end AS modus,
  case which when 1 then AVG(duration) else COUNT(duration) end AS value
FROM test_data, (select 1 as which union all select 2) x
GROUP BY MODULE, modus, which

SELECT *
FROM (
 SELECT MODULE,
  modus + '_' + case which when 1 then 'AVG' else 'COUNT' end AS modus,
  case which when 1 then CAST(AVG(1.0*duration) AS NUMERIC(10,2)) else COUNT(duration) end AS value
 FROM test_data, (select 1 as which union all select 2) x
 GROUP BY MODULE, modus, which
) P
PIVOT (MAX(value) FOR modus in ([A_AVG], [A_COUNT], [B_AVG], [B_COUNT])
) AS pvt
ORDER BY pvt.MODULE

In the example above, AVG and COUNT are compatible (count - int => numeric). If they are not, convert both explicitly to a compatible type.

Note - The first query shows AVG for M2/A as 2, due to integer averaging. The 2nd (pivoted) query shows the actual average taking into account decimals.