how to find (A+B)/C from a single row when we assign grades to them

Question

question

nishan440 asked Dec 3, '20

how to find (A+B)/C from a single row when we assign grades to them

Table Name - TEST_SOFT1

Column name- ITEM_CODE and GRADE

so in ITEM_CODE there is like 10000 itemcode and in GRADES table we gave grades(A B and C) to them(itemcode).

and the grades are A,B and C.

so now we already gave the grades to all item_code.so we can assume that total itemcode grades is A=7000, B= 3000 and C= 1000

question

and we need to find (A+B)/C in numbers means how many grades we have?

sample record

oracle plsql 11g

screenshot-2020-12-03-120309.png (91.3 KiB)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2020-12-03T09:43:37Z

anthony.green answered Dec 3, '20

So this is the total regardless of the item_code

(SUM(A)+SUM(B))/SUM(C)

(7000+3000)/1000 = 10

So the answer you want for this would be 10

4

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

anthony.green commented · Dec 03, 2020 at 10:33 AM

create table #test_soft1 (item_code int, grade char(1))

GO

insert into #test_soft1 values (1,'A')

GO 7000

insert into #test_soft1 values (2,'B')

GO 3000

insert into #test_soft1 values (3,'C')

GO 1000

select

(sum(a_cnt)+sum(b_cnt))/sum(c_cnt)

from

(

select

case when grade = 'A' then 1 end as A_Cnt,

case when grade = 'B' then 1 end as B_Cnt,

case when grade = 'C' then 1 end as C_Cnt

from #test_soft1

) as cnts

0 ·

anthony.green commented · Dec 03, 2020 at 10:37 AM

This is T-SQL code, just noticed you tagged this as PL/SQL as such you will need to convert it to PL/SQL.

Note that this is a Microsoft SQL Server forum, not an Oracle forum, for Oracle questions your best posting on a dedicated Oracle forum, you may get lucky and catch someone here who knows Oracle, but this is a Microsoft SQL Server discussion site.

0 ·

nishan440 anthony.green commented · Dec 03, 2020 at 10:52 AM

it is pl/sql question . not t-sql ..thanx for you help btw.

my question is i have one table they have item_code and grade column

like sample record there is numbeers of record in it and all assign by grades A,B and C

i need to find that numbers like mathematic problem.

(A+B)/C plz help me if u can ...u can ask me more details if u want

A=5000 B= 3000 that is just a example that how many grade are there

we need to find the total number in a single query

0 ·

anthony.green nishan440 commented · Dec 03, 2020 at 11:42 AM

You didn't answer the question, you said 7000 A's, 3000 B's, 1000 C's, so the answer with your math is 10. Is that correct?

The solution I gave is T-SQL but you will need to translate that to PL/SQL.

This is a T-SQL Microsoft SQL Server forum. If you need specific Oracle help you need to post on an Oracle forum

0 ·

question