Conditional Sum on Oracle

Conditional SUM on oracle SQL

You can use SUM(CASE ... ):

SELECT item, location,
   SUM(CASE WHEN quantity > 0 THEN quantity ELSE 0 END) AS positive_sum,
   SUM(CASE WHEN quantity < 0 THEN quantity ELSE 0 END) AS negative_sum
FROM your_table
GROUP BY item, location;

LiveDemo

Oracle SQL Conditional SUM

This is a pretty easy way to express the logic:

SELECT name,
       (CASE WHEN MAX(my_value) = 'UNLIMITED' THEN 'UNLIMITED'
             ELSE TO_CHAR(SUM(CASE WHEN my_value <> 'UNLIMITED' THEN my_value END))
        END)
FROM my_table
GROUP BY name;

This uses the fact that characters are ordered after numbers.

Or similar logic:

SELECT name,
       (CASE WHEN COUNT(*) <> COUNT(NULLIF(my_value, 'UNLIMITED')) THEN 'UNLIMITED'
             ELSE TO_CHAR(SUM(NULLIF(my_value, 'UNLIMITED')))
        END)
FROM my_table
GROUP BY name;

Conditional SUM on Oracle

As an alternative to recursive SQL, you can also use the SQL MODEL clause. Personally, I find this a little easier to read than recursive SQL, though it is harder to write (because most people, like me, need to look up the syntax).

-- "test_data" is just a substitute for your real table, which I don't have
-- it is just so people without your table can run this example and would
-- not be part of your real solution.
with test_data ( sort_col, addend ) as
( SELECT 'A', 3 FROM DUAL UNION ALL
 SELECT 'B', 7 FROM DUAL UNION ALL
 SELECT 'C', 6 FROM DUAL UNION ALL
 SELECT 'D', 5 FROM DUAL UNION ALL
 SELECT 'E', 9 FROM DUAL UNION ALL
 SELECT 'F', 3 FROM DUAL UNION ALL
 SELECT 'G', 8 FROM DUAL ),
-- Solution begins here
sorted_inputs ( sort_col, sort_order, addend, running_sum_max_15) as
( SELECT sort_col, row_number() over ( order by sort_col ) sort_order, addend, 0 from test_data )
SELECT sort_col, addend, running_sum_max_15
from sorted_inputs
model 
dimension by (sort_order)
measures ( sort_col, addend, running_sum_max_15 )
rules update
( running_sum_max_15[1] = addend[1],
  running_sum_max_15[sort_order>1] = 
          case when running_sum_max_15[CV(sort_order)-1] < 15 THEN 
             running_sum_max_15[CV(sort_order)-1] ELSE 0 END+addend[CV(sort_order)]
)

RESULTS

+----------+--------+--------------------+
| SORT_COL | ADDEND | RUNNING_SUM_MAX_15 |
+----------+--------+--------------------+
| A        |      3 |                  3 |
| B        |      7 |                 10 |
| C        |      6 |                 16 |
| D        |      5 |                  5 |
| E        |      9 |                 14 |
| F        |      3 |                 17 |
| G        |      8 |                  8 |
+----------+--------+--------------------+

Grouped conditional sum in Oracle SQL

If these two queries gives what you want and you need to merge them then only ma_2 needs conditional sum:

select person_id, 
       sum(case when months_before <= 2 then balance end) / 2  as ma_2,
       sum(balance) / 3 as ma_3
  from my_table
  where months_before <= 3
  group by person_id

^dbfiddle

Oracle SQL Sum with conditions

You need conditional aggregation, to avoid repeating the expressions nest them in a Derived Table (Inline View in Oracle speak):

select
    Department
  ,sum(case when Wage_type = 'SICK' then Hours_worked else 0 end) as HoursSick
  ,sum(case when Wage_type = 'Normal' then Hours_worked else 0 end) as HoursPresent
  ,"Month_Number"
  ,"Year"
from
 (
    Select 
     (case 
        when TPDRS.ORG_CODE = 1010 then  'Trolley P1'
        when TPDRS.ORG_CODE = 1011 then  'Trolley P2'
        when TPDRS.ORG_CODE = 1053 then  'Trolley P3'
        when TPDRS.ORG_CODE between 1054 and 1057 then  'Trolley P4'
        when TPDRS.ORG_CODE between 1040 and 1047 then  'Trolley P5'
        when TPDRS.ORG_CODE in ('1063','1064','1065','1068') then 'Trolley P6'
        else 'NOT REQUIRED'
            end) as Department
    ,EXTRACT(Month from TPDRS.ACCOUNT_DATE) "Month_Number"
    ,EXTRACT(Year from TPDRS.ACCOUNT_DATE) "Year"

    from TrolleyParkAttendant  TPDRS
    WHERE TPDRS.ACCOUNT_DATE > '01/DEC/2017'
     AND (TPDRS.WAGE_GRP_DB IN ( 'O', 'N' ) or WAGE_CODE = 'SICK')
 ) dt
group by
    Department
  ,"Month_Number"
  ,"Year"

Oracle Sum Based On Condition

length(substr(DATA_STRING, instr(DATA_STRING,'P')+1))? This is doing a lot of work.

From what I can tell, you want sum of the number of characters after a 'P' or 'F' in the string. If so:

SELECT ID,
       SUM(CASE WHEN data_string like '%P%'
                THEN len(DATA_STRING) - (instr(DATA_STRING, 'P') + 1) ELSE 0
           END) as Num_P,
       SUM(CASE WHEN data_string like '%F%'
                THEN len(DATA_STRING) - (instr(DATA_STRING, 'F') + 1) ELSE 0
           END) as Num_F
FROM DATA P JOIN
     PLC PP 
     ON P.ID = PP.ID 
GROUP BY ID;

I also fixed the join syntax to use explicit joins.

SQL sum of two conditional aggregation

You probably won't see a significant performance hit from what you're doing now as you already have all the data available, you're just repeating the case evaluation.

But you can't refer to the column aliases for the first two columns within the same level of query.

If you can't do a simple count as @Zeki suggested because you aren't sure if there might be values other than zero and one (though this looks rather like a binary true/false equivalent, so there may well be a check constraint limiting you to those values), or if you're just more interested in a more general case, you can use an inline view as @jarhl suggested:

select Year_CW,
       "Total_sampled(Checked)",
       "Total_unsampled(Not_Checked)",
       "Accepted",
       "Accepted with comments",
       "Request for rework",
       "Rejected",
       "Total_sampled(Checked)" + "Total_unsampled(Not_Checked)" as "Total_DS"
from (
  select Year_CW,
         sum(case when col = 0 then 1 else 0 end) as "Total_sampled(Checked)",
         sum(case when col = 1 then 1 else 0 end) as "Total_unsampled(Not_Checked)",       
         sum(case when col = 0 AND col2 = 'accepted' then 1 else 0 end) as "Accepted",
         sum(case when col = 0 AND col2 = 'accepted with comments' then 1 else 0 end)
           as "Accepted with comments",
         sum(case when col = 0 AND col2 = 'request for rework' then 1 else 0 end)
           as "Request for rework",
         sum(case when col = 0 AND col2 = 'rejected' then 1 else 0 end) as "Rejected"
  from (
    select Year_CW, SAMPLED as col, APPROVAL as col2 
    from View_TEST tv
  ) tv
  group by Year_CW 
)
order by Year_CW desc;

The inner query gets the data and calculates the conditional aggregate values. The outer query just gets those values from the inner query, and also adds the Total_DS column to the result set by adding together the rwo values from the inner query.

---

You should generally avoid quoted identifiers, and if you really need them in your result set you should apply them at the last possible moment - so use unquoted identifiers in the inner query, and give them qupted aliases in the outer query. And personally if the point of a query is to count things, I prefer to use a conditional count over a conditional sum. I'm also not sure why you already have a subquery against your view, which just changes the column names and makes the main query slightly more obscure. So I might do this as:

select year_cw,
       total_sampled_checked as "Total_sampled(Checked)",
       total_unsampled_not_checked as "Total_unsampled(Not_Checked)",
       accepted as "Accepted",
       accepted_with_comments as "Accepted with comments",
       request_for_rework as "Request for rework",
       rejected as "Rejected",
       total_sampled_checked + total_unsampled_not_checked as "Total_DS"
from (
  select year_cw,
         count(case when sampled = 0 then 1 end) as total_sampled_checked,
         count(case when sampled = 1 then 1 end) as total_unsampled_not_checked,       
         count(case when sampled = 0 and approval = 'accepted' then 1 end) as accepted,
         count(case when sampled = 0 and approval = 'accepted with comments' then 1 end)
           as accepted_with_comments,
         count(case when sampled = 0 and approval = 'request for rework' then 1 end)
           as request_for_rework,
         count(case when sampled = 0 and approval = 'rejected' then 1 end) as rejected
  from view_test
  group by year_cw 
)
order by year_cw desc;

Note that in the case expression, then 1 can be then <anything that isn't null>, so you could do then sampled or whatever. I've left out the implicit else null. As count() ignores nulls, all the case expression has to do is evaluate to any not-null value for the rows you want to include in the count.

How sum with case conditional statement works in sql

Presumably, this is the part that you are struggling to understand:

  select deptno,
         sum(case when jobname = 'Analyst' then 1 else 0 end) as numAnalysts
  from employees
  group by deptno

This is a simple aggregation query, really. What the query is doing is:

• Look at each row in employees
• If jobname is 'Analyst' then assign the value of 1 (this is the case statement. Otherwise, assign a value of0`.
• Aggregate by department, summing the value just calculated. This has the effect of counting the number of analysts.

case is an expression that returns a value. The sum() is simply adding up that value for each group.

Oracle SQL - Running Sum based on group by and condition

You could make a first window sum() to define the groups, then use it as partition for the outer query:

select 
    t.*,
    sum(count1) over(partition by id, grp order by seq) running_count
from (
    select 
        t.*,
        sum(label) over(partition by id order by seq desc) grp
    from mytable t
) t

Demo on DB Fiddle:

ID | SEQ | LABEL | COUNT1 | GRP | RUNNING_COUNT
-: | --: | ----: | -----: | --: | ------------:
 1 |   1 |     0 |      3 |   1 |             3
 1 |   2 |     0 |      2 |   1 |             5
 1 |   3 |     0 |      6 |   1 |            11
 1 |   4 |     1 |      2 |   1 |            13
 1 |   5 |     0 |      3 |   0 |             3
 1 |   6 |     0 |      5 |   0 |             8
 2 |   1 |     0 |      2 |   1 |             2
 2 |   2 |     1 |      1 |   1 |             3
 2 |   3 |     0 |      3 |   0 |             3

---

Related Topics