Group by when using pivot?

Question

I have a table which shows data for each of the dates on a column, but on each of these days there are 1000 values and I would like to get the mean of these values, but do not know how to use group by with pivot.

SQL so far:

 select *
    from (select date_data,id, name, scenery, 
                 (extract(month from date)-extract(month from data_date))+12*(extract(year from date)-extract(year from data_date)) as T ,simulation,value   
            from escen
            where date_data = '30/09/2022'
              and scenario in ('BASE')
          )    
    pivot ( avg(value) 
            for T between 0 and 120
    order by 1, 2, 3, 4;

This returns a pivoted table with the same 1000 values instead of the average value. I should group it either by id or name, but I am not sure how.

Data:

date_data|ID|Name|Scenery|N Simu|Simul|   Date   | Value
30/09/22 |1|  A  |  Base | 1000 |  1  | 30/09/28 | 0,0397
30/09/22 |1|  A  |  Base | 1000 |  2  | 30/09/28 | -0,069

I have different Scenarios and 1000 simulations for each of those. The simul column shows which number of simulation is of these 1000, and I would like to get the mean of these 1000 values for each scenario in each date.

Current results:

date_data|ID|Name|Scenery|Simul|   30/09/28   |  31/10/28
30/09/22 |1|  A  |  Base |  1  |    0,0397    |  0,0521
30/09/22 |1|  A  |  Base |  2  |    -0,069    |  -0,0222

Desired results:

date_data|ID|Name|Scenery|     30/09/28     |  31/10/28
30/09/22 |1|  A  |  Base |    avg(value)    |  avg(value)

That's not SQL Server syntax. Which DBMS are you *actually* using? — Charlieface, Oct 30 '22 at 12:28
My bad, I am not too familiar with SQL in general. I use SQL Developer - Oracle. — Guillem, Oct 30 '22 at 12:37
Which of the `simul` values should be in the output? You need to exclude this column from the select list or perform some calculation, which would result in single value per all days. Otherwise you'll get as much rows as there are dimension values (those who are not `pivot`ed) — astentx, Oct 30 '22 at 13:47
Since it's the average of the 1000 simulations, I would not need that column. Thanks! — Guillem, Oct 30 '22 at 13:51

d r · Accepted Answer · 2022-10-30T13:27:51.100

If your current result is:

A_DATE	ID	NAME	SCENERY	SIMUL	'30/09/28'	'31/10/28'
30-SEP-22	1	A	Base	1	.0397	.0521
30-SEP-22	1	A	Base	2	-0.069	-0.0222

then you can do it like below.
The WITH clause is here just to generate some sample data and it is not part of the answer.

WITH
    tbl_a AS
        (
            Select To_Date('30/09/22', 'dd/mm/yy') "A_DATE", 1 "ID", 'A' "NAME", 'Base' "SCENERY", 1 "SIMUL", 0.0397 "'30/09/28'", 0.0521 "'31/10/28'" From Dual Union All
            Select To_Date('30/09/22', 'dd/mm/yy') "A_DATE", 1 "ID", 'A' "NAME", 'Base' "SCENERY", 2 "SIMUL", -0.069 "'30/09/28'", -0.0222 "'31/10/28'" From Dual 
        )
SELECT
    A_DATE, ID, NAME, SCENERY, Min(SIMUL) "SIMUL", Avg(tbl_a."'30/09/28'") "'30/09/28'", Avg(tbl_a."'31/10/28'") "'31_10_28'"
FROM
    tbl_a
GROUP BY
    A_DATE, ID, NAME, SCENERY

The result should be:

A_DATE	ID	NAME	SCENERY	SIMUL	'30/09/28'	'31/10/28'
30-SEP-22	1	A	Base	1	-0.01465	.01495

And, when using pivoting, it is better to give aliases to the columns. Then there will be less complex sintax needed to do the job. Regards...

Group by when using pivot?

1 Answers1