Need to create multiple fields that aggregate the data-CodePudding

Need to aggregate the data multiple times (too many times) how can I achieve this without inserting the inner select statement for each aggregation. Don't want to do a union as the query will become too big, would rather do it the way I have shown below but not sure what to put in the from clause I don't want to insert in there the inner select clause as below is just a example the real inner select statement I am working with is 10x bigger. For example

select (
  select count(id)
  from ?
  where date_1 = 2022
  and date_2 = 2021
)
(
  select sum(sales)
  from ?
  where date_1 = 2020
  and date_2 = 2018
)
(
  select sum(profit)
  from ?
  where date_1 = 2020
  and date_2 = 2021
)
from (
  select id,
  profit,
  sales,
  date 1,
  date_2
  from orders_table
)

CodePudding user response：

If you want your output as columns (rather than rows) then you can use a PIVOT:

SELECT id,
       sales,
       profit_22_21,
       profit_20_18,
       profit_20_21
FROM   orders_table
PIVOT (
  SUM(profit)
  FOR (date_1, date_2) IN (
    (2022, 2021) AS profit_22_21,
    (2020, 2018) AS profit_20_18,
    (2020, 2021) AS profit_20_21
  )
)

Which, for the sample data:

CREATE TABLE orders_table (id, sales, date_1, date_2, profit) AS
SELECT 1, 'X', 2022, 2021, 10 FROM DUAL CONNECT BY LEVEL <= 10 UNION ALL
SELECT 1, 'X', 2020, 2018, 10 FROM DUAL CONNECT BY LEVEL <=  7 UNION ALL
SELECT 1, 'X', 2020, 2021, 10 FROM DUAL CONNECT BY LEVEL <=  3 UNION ALL
SELECT 1, 'Y', 2022, 2021, 10 FROM DUAL CONNECT BY LEVEL <=  1 UNION ALL
SELECT 1, 'Y', 2020, 2018, 10 FROM DUAL CONNECT BY LEVEL <=  2 UNION ALL
SELECT 1, 'Z', 2020, 2021, 10 FROM DUAL CONNECT BY LEVEL <=  4 UNION ALL
SELECT 2, 'X', 2022, 2021, 10 FROM DUAL CONNECT BY LEVEL <=  6 UNION ALL
SELECT 2, 'X', 2020, 2018, 10 FROM DUAL CONNECT BY LEVEL <=  8 UNION ALL
SELECT 2, 'X', 2020, 2021, 10 FROM DUAL CONNECT BY LEVEL <=  9;

Outputs:

ID SALES PROFIT_22_21 PROFIT_20_18 PROFIT_20_21

2 X 60 80 90

1 Y 10 20 null

1 Z null null 40

1 X 100 70 30

ID	SALES	PROFIT_22_21	PROFIT_20_18	PROFIT_20_21
2	X	60	80	90
1	Y	10	20	null
1	Z	null	null	40
1	X	100	70	30

db<>fiddle here

CodePudding user response：

Aggregation functions return only row, but you can use CASE WHEN THEN to select the data you want

CREATE TABLE orders_table 
(id int, profit DECIMAL(10,2), sales DECIMAL(10,2),date_1 date, date_2 date)

select 
  count(CASE WHEN EXTRACT(YEAR FROM date_1) = 2022
  and EXTRACT(YEAR FROM date_2) = 2021 THEN id END),



  sum(CASE WHEN EXTRACT(YEAR FROM date_1) = 2019
  and EXTRACT(YEAR FROM date_2) = 2018 THEN sales END ),
  

  sum(CASE WHEN EXTRACT(YEAR FROM date_1) = 2010
  and EXTRACT(YEAR FROM date_2) = 2021 THEN  profit END)

from (
  select id,
  profit,
  sales,
  date_1,
  date_2
  from orders_table
) t1

COUNT(CASEWHENEXTRACT(YEARFROMDATE_1)=2022ANDEXTRACT(YEARFROMDATE_2)=2021THENIDEND) | SUM(CASEWHENEXTRACT(YEARFROMDATE_1)=2019ANDEXTRACT(YEARFROMDATE_2)=2018THENSALESEND) | SUM(CASEWHENEXTRACT(YEARFROMDATE_1)=2010ANDEXTRACT(YEARFROMDATE_2)=2021THENPROFITEND)
----------------------------------------------------------------------------------: | -----------------------------------------------------------------------------------: | ------------------------------------------------------------------------------------:
                                                                                  0 |                                                                                 null |                                                                                  null

db<>fiddle here

CodePudding user response：

You can use conditional aggregation; something like:

select count(case when date_1 = 2022 and date_2 = 2021 then id end),
  sum(case when date_1 = 2020 and date_2 = 2018 then sales else 0 end),
  sum(case when date_1 = 2020 and date_2 = 2021 then profit else 0 end)
from orders_table

or if sales/profit can be null, use nvl or coalesce so the sum doesn't end up null:

select count(case when date_1 = 2022 and date_2 = 2021 then id end),
  sum(coalesce(case when date_1 = 2020 and date_2 = 2018 then sales end, 0)),
  sum(coalesce(case when date_1 = 2020 and date_2 = 2021 then profit end, 0))
from orders_table

The count() function only counts non-null values; the case expression only evaluates to a non-null value when the years conditions are matched, so it only counts those matching IDs.

The sum() function will return null if any value is null, so for that you either need to have else 0 to give a nominal value for the non-matching rows, or coalesce to zero so non-matching rows - and any potential null matching values - don't cause the whole sum to be null.

You could also change your 'inner select clause' from an inline view (which you'd be trying to reference too many levels down) to a common table expression (CTE):

with cte as (
  select id,
  profit,
  sales,
  date_1,
  date_2
  from orders_table
)
select (
  select count(id)
  from cte
  where date_1 = 2022
  and date_2 = 2021
),
(
  select sum(sales)
  from cte
  where date_1 = 2020
  and date_2 = 2018
),
(
  select sum(profit)
  from cte
  where date_1 = 2020
  and date_2 = 2021
)
from dual

but conditional aggregation (or pivoting depending on what you need) is probably going to be simpler and easier to maintain than multiple subqueries.

db<>fiddle with some very dummy data.