CODEWITHSUNDEEP
sqlsql-server-2005oracle-databasepivot
bernd_k
bernd_k

Reputation: 11966

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

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?

Upvotes: 5

Views: 8982

Answers (3)

bernd_k
bernd_k

Reputation: 11966

Solution for Oracle 11g + : 

create table test_data (
    module varchar2(30),
    modus   varchar2(30),
    duration Number(10)
);


insert into test_data values ('M1', 'A', 5); 
insert into test_data values ('M1', 'A', 5); 
insert into test_data values ('M1', 'B', 3); 
insert into test_data values ('M2', 'A', 1); 
insert into test_data values ('M2', 'A', 4); 


select   *
FROM (
select   *
from test_data
) 
PIVOT (
    AVG(duration) avg , count(duration) count
    FOR modus in ( 'A', 'B')
) pvt
ORDER BY pvt.module;

I do not like the column names containing apostrophes, but the result contains what I want:

MODULE                            'A'_AVG  'A'_COUNT    'B'_AVG  'B'_COUNT
------------------------------ ---------- ---------- ---------- ----------
M1                                      5          2          3          1
M2                                    2.5          2                     0

I really wonder what the Microsoft boys did, when they only allowed one aggregate function within pivot. I call evaluation avgs without accompanying counts statistical lies.

SQL-Server 2005 + (based on Cyberwiki):

CREATE TABLE test_data (
    MODULE VARCHAR(30),
    modus   VARCHAR(30),
    duration INTEGER
);


INSERT INTO test_data VALUES ('M1', 'A', 5); 
INSERT INTO test_data VALUES ('M1', 'A', 5); 
INSERT INTO test_data VALUES ('M1', 'B', 3); 
INSERT INTO test_data VALUES ('M2', 'A', 1); 
INSERT INTO test_data VALUES ('M2', 'A', 4); 


SELECT MODULE, modus, ISNULL(LTRIM(STR(AVG(duration))), '') + '|' + ISNULL(LTRIM(STR(COUNT(duration))), '') RESULT
FROM test_data
GROUP BY MODULE, modus;

SELECT   *
FROM (
SELECT MODULE, modus, ISNULL(LTRIM(STR(AVG(duration))), '') + '|' + ISNULL(LTRIM(STR(COUNT(duration))), '') RESULT
FROM test_data
GROUP BY MODULE, modus
) T
PIVOT (
 MAX(RESULT)
 FOR modus in ( [A], [B])
) AS pvt
ORDER BY pvt.MODULE

result:

MODULE                         A                     B
------------------------------ --------------------- ---------------------
M1                             5|2                   3|1
M2                             2|2                   NULL

Upvotes: 1

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

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.

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453288

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

Upvotes: 3

Related Questions