Geek Answers Handbook

How can I use PIVOT to display the average and count in my cells?

Looking at the syntax, I get the strong impression that PIVOT does not support anything but one aggregate function that needs to be calculated for a cell.

From a statistical presentation showing only some average values, without citing the number of cases the average value refers to, it is very unsatisfactory (this is a polite version).

Is there any good template for evaluating avg and pivots based count points and mixing them together to give a good result?

+5
source share
3 answers

.. . , , varchar?

, .

PIVOT , , (, MAX/MIN) , - aggregate query .

bernd_k oracle/mssql, SQL Server. .

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

AVG COUNT (count - int = > numeric). , .

. AVG M2/A 2 - . () .

+1

, cross tab. PIVOT - , .

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

, 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
+3

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;

, , , :

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

, Microsoft, . avgs count .

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
+1
source

More articles:


All Articles