Agregate Rows in Oracle SQL Statement

Oracle SQL - Generate aggregate rows for certain rows using select

One approach would be to use a union:

WITH cte AS (
    SELECT "FILE", ID, PARENTID, SHOWCHILD, CAT1, CAT2, CAT3, TOTAL, 1 AS position
    FROM yourTable
    UNION ALL
    SELECT 'Tot', 'Res', 'Res', 'N', SUM(CAT1), SUM(CAT2), SUM(CAT3), SUM(TOTAL), 2
    FROM yourTable
    WHERE SHOWCHILD = 'Y'
)

SELECT "FILE", ID, PARENTID, SHOWCHILD, CAT1, CAT2, CAT3, TOTAL
FROM cte
ORDER BY
    position,
    "FILE";

Demo

Oracle SQL aggregate rows into column listagg with condition

You can use case:

SELECT listagg(CASE WHEN content = 'FRAGMENT' THEN c.data END, ',') WITHIN GROUP (ORDER BY c.order) as fragments,
       listagg(CASE WHEN content = 'BULK' THEN c.data END, ',') WITHIN GROUP (ORDER BY c.order) as bulks
FROM content c
WHERE c.blockId = 330;

Oracle - Aggregation of rows and null handling for partitioned set

You can use window functions to fill in the missing data -- in this case last_value() with the ignore nulls option:

select case_no, line_no,
       max(case when column_name = 'REASON_TEXT' then new_entry end) as line_reason,
       last_value( max(case when column_name = 'REASON_TEXT' then new_entry end) ignore nulls
                 ) over
                 (partition by case_no order by line_no 
                 ) as imputed_reason_text
from etl_test
group by case_no, line_no
order by 1, 2;

Here is a SQL Fiddle.

Oracle SQL query with Pivot and aggregate function

If I understand what you're trying to do, I'd move the decision on which column to use up into the main query, with a case expression inside a single sum():

SELECT unique state_code, city_code, 
  equipment_type, 
  sum(case when equipment_type in ('Gabion Basket',
      'Rapid Deployment Floodwall', 'Portable Coffer Dam')
    then AVAILABLE_COUNT_LENGTH
    else AVAILABLE_COUNT end) AS chosen_count
FROM EQUIP_VIEW_TABLE
GROUP BY state_code, city_code, equipment_type

and leave the pivot as it was:

SELECT * 
FROM ( SELECT unique state_code, city_code, 
     equipment_type, 
     sum(case when equipment_type in ('Gabion Basket',
         'Rapid Deployment Floodwall', 'Portable Coffer Dam')
       then AVAILABLE_COUNT_LENGTH
       else AVAILABLE_COUNT end) AS chosen_count
     FROM EQUIP_VIEW_TABLE
     GROUP BY state_code, city_code, equipment_type )
pivot (max(chosen_count)
     for (equipment_type) in 
     ('Sandbag' "CNT_SANDBAG",
      'Gabion Basket' "CNT_GABION_BASKET",
      'Rapid Deployment Floodwall' "CNT_RAPID_DEPLOYMENT_FLOODWALL",
      'Portable Coffer Dam' "CNT_PORTABLE_COFFER_DAM",
      'Polyethylene Sheeting' "CNT_POLYETHYLENE_SHEETING",
      'Pump' "CNT_PUMP"))

Oracle group by with different condition for each aggregate function

You can try below - using case when expression

select order, count(case when  order_price > 100 then order end), 
sum(case when order_id < 200 then order_price end), 
avg(case when other condition then order_price end)
from table_orders
group by order

An SQL query that combines aggregate and non-aggregate values in one row

You can use an analytic function to calculate a total over multiple rows of a result set, then filter out the rows you don't want.

Leaving out all the extra columns for sake of brevity:

SELECT cust_line, qty_invoiced, order_total/qty_invoiced AS price
FROM (
  SELECT l.cust_line, qty_invoiced,
    SUM(total) OVER (PARTITION BY l.cust_line) AS order_total,
    COUNT(cust_line) OVER (PARTITION BY l.cust_line) AS group_count
  FROM
  (SELECT CUST_LINE, ORDER_NO, LINE_NO
   FROM CUSTOMER_ORDER_LINE
   GROUP BY CUST_LINE, ORDER_NO, LINE_NO
   ) L
  INNER JOIN CUSTOMER_ORDER_IVC_REP I
  ON I.ORDER_NO = L.ORDER_NO
  WHERE RESULT_KEY = 999999
  AND I.LINE_NO = L.LINE_NO
)
WHERE ( cust_part = 'GROUPINV' OR group_count = 1 )
ORDER BY cust_line

I am guessing on what you want in the PARTITION BY clause; this is essentially a GROUP BY that applies only to the SUM function. Not sure if you might also want order_no in the partition.

The trick is to select all the rows in the inner query, applying SUM across them all; then filter out the rows you are not interested in in the outermost query.

Aggregate function to multiply all values

Yes, there's a technique described here in this blog:

Basically you take the natural log of SUM and then do an exponential (e^x)

SELECT EXP (SUM (LN (col))) as product from t;

Since the output of this would be a floating point, you may do a FLOOR

FLOOR( EXP (SUM (LN (col))) ) 

DEMO

Note: I've just found that this would fail if one of the rows has 0. so you should use a separate condition or a with clause that if one of them is zero the product should be zero.

Oracle query aggregate function

Here is the solution, please check it out...

select mobile_no,sim_no,start_date,end_date from(
select mobile_no,sim_no,start_date,end_date,rank() over(partition by mobile_no,sim_no order by start_date desc) rn from employee_1)s
where rn=1
and exists (select 1 from mobile_1 m where m.Mobile_No = s.mobile_no);

---

Related Topics