How To Count All Tuples Then Compute GPA Pertaining To ID
Apr 1, 2011
I'm a student researching SQL, Oracle queries. I'm currently writing a oracle:JDBC to query by id, then receive all tuples that belong to id and compute GPA. I convert letter-grade to # in query how to count rows pertaining to id, then average GPA of all tuples. I understand Count(*) where sid=?, but i need to count the rows for id, then convert letter to number, finally compute gpa for all pertaining tuples belonging to id.
Everything works find but the last query for my JDBC prog.Here is my query as of now...
"SELECT grade, CASE WHEN grade = 'F' THEN 0 WHEN grade = 'D' THEN 1 WHEN grade = 'C' THEN 2 WHEN grade = 'B' THEN 3 WHEN grade= 'A' THEN 4 END AS letter_grade FROM Grades"
How do I count all tuples then compute the gpa pertaining to id.For all IDs?
View 8 Replies
ADVERTISEMENT
Jul 9, 2013
Is that possible to modify the ***** and ----- in the TEXT report?
DEPARTMENT_ID JOB_ID LAST_NAME SALARY
============= ========== ========================= ==========
20 MK_MAN Hartstein 13000
************* ********** ----------
sum 13000
From above lines, you can see ****** and ----- which are above sum&13000.
Is that possible to change them as '='?
DEPARTMENT_ID JOB_ID LAST_NAME SALARY
============= ========== ========================= ==========
20 MK_MAN Hartstein 13000
============= ========== ==========
sum 13000
"set underline =" is only shot the headers.
View 3 Replies
View Related
Aug 28, 2010
1) Why we need to do Compute Statistic on index.
2) Is it only for optimizer to make a better plan?
3) If yes, which means, optimizer will not able to collect statistic by itself?
4) if I'm not collect statistic, then optimizer will do it or skip.
View 5 Replies
View Related
Sep 18, 2012
how can i compute row wise multiplication for each unique value of id. I am trying to get the output as showing id and their respective results after multiplying the corresponding values.
CREATE TABLE mult_tbl(
id NUMBER,
val number
);
insert all
into mult_tbl values (1,2)
into mult_tbl values (1,3)
[code]....
View 11 Replies
View Related
May 15, 2013
I have to create some indexes in a production database. Do I need to Compute Statistics after creating indexes? Or when I create they automatically are computed?
The version I'm using is:
Oracle Database 10g Release 10.2.0.5.0 - 64bit Production
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for 64-bit Windows: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
View 2 Replies
View Related
Jul 1, 2012
My issue is with a calculation using columns as variables. below is the code to create and attached is the code (INSERT_data.txt) to fill-in the required tables....
what's required here?
I need to update (through update or simply create new table statement)
BPF(i), BPC(i), BPY(i) of table root_tbl (A) based on InF_IDX, InC_IDX & InY_IDX and OutF_IDX, OutC_IDX & OutY_IDX using BPF(i), BPC(i), BPY(i) from table pvt_vectors_tbl(B).
Join Clause: A.P_NODE=B..P_NODE and
A.C_NODE=B.C_NODE and
A.P_NODE_Date=B.P_NODE_Date and
A.C_NODE_Date=B.C_NODE_Date
the formula are:
BPF(i):
if A.InF_IDX<=A.OutF_IDX then
FOR i=1 to A.OutF_IDX+1
if i<InF_IDX then A.BPF(i)= A.BPF(i)+ B.BPF(OutF_IDX + i - InF_IDX); else A.BPF(i)=0;
i=i+1;
else
FOR i=1 to A.InF_IDX+1
if i<OutF_IDX then A.BPF(i)= A.BPF(i)+ B.BPF(InF_IDX + i - OutF_IDX); else A.BPF(i)=0;
i=i+1;
idem for BPc and BPY.
in real word:
* root_tbl table has ~2 million records, 50 BPF(i) columns, 50 BPC(i) columns and ~475 BPY(i) columns
* pvt_vectors_tbl has ~50.000 records
/*create and fill-in pvt_vectors_tbl table*/
CREATE TABLE root_tbl
(
P_NODE VARCHAR2(3),
C_NODE VARCHAR2(3),
P_NODE_Date date,
C_NODE_Date date,
InF_IDX number,
InC_IDX number,
InY_IDX number,
[code].........
View 2 Replies
View Related
Aug 14, 2011
I am new to PL/SQL and how to create a trigger to compute the population of the school from the groups of students and store back in population. It also needs to check that there is a min of 10 students to a school.
CREATE OR REPLACE TYPE group_type AS OBJECT
(
group_nameVARCHAR2(20),
tutor_idNUMBER(5),
[code]...
View 11 Replies
View Related
Jan 30, 2012
i have a Field item on my report which i would like to show "Price INCL of VAT". Any PL/SQL code or trigger to do this? VAT is 16%
I'm a Forms programmer, not so familiar with Reports.I suspect, it should be something like this:
F_1 := F_1 * 1.16
View 25 Replies
View Related
Nov 19, 2011
the problem below:
I have a table AlertData below:
DeptIDMONTHCount
192010-041392
192010-051134
192010-061094
192010-071333
292010-042217
[Code]...
Within each DeptID group I need to calculate absolute change of 'Count' column between previous and current months and compare change value with threshold.
If ratio >= threshold N number of times I need to make a note of that event. Threshold = 0.1 N = 2 - alert needs to exceed threshold two consequtive times
Here is data processing algorithm:
1. Calculate change between month 2010-04 and 2010-05: abs((1134/1392 - 1))= 0.18;
2. check change value against threshold: 0.18 > 0.1
3. Threshold was exceeded, set alert_fired_cnt counter to = 1
4. Once alert fired it creates a baseline for comparison - I need to use Count from month 2010-04: We're now in month 2010-06: abs(1094 / 1392 - 1)=0.21
5. check change value against threshold: 0.21 > 0.1
6. Threshold was exceeded, increment alert_fired_cnt counter by 1 = 2
7. At this point alert exceede threshold two times, I need to set a alert_triggered flag = 1 and reset alert_fired_cnt = 0 for further calculations
8. We're in montn 2010-07: abs(1333/1294-1)=0.03
8. check change value against threshold: 0.03 < 0.1
9. Since threshold was not exceeded, keep alert_fired_cnt counter to = 0
Above algorithm needs to be run for all DeptID groups.
I load above data into an associative array and loop through elements. I am having trouble keeping computations within each DeptID group.
View 18 Replies
View Related
Mar 13, 2013
I have an interactive report with two columns timestamp type. I want to use the compute action in order to get the difference between those dates. Nevertheless I got values like this: +000000008 00:00:00.000000.
View 1 Replies
View Related
Oct 14, 2010
I tried BRK and COMPUTE commands myself after reading the documentation but its not working...The output of my script is correct but What I want is the information to be displayed on a different way:
Original script for the report:
set serveroutput on size 1000000
set pages 10000
set lines 1000
set arraysize 1
set trimspool on
set heading off
[code]....
The output should be exactly as above as the requirement is this should be in this particular order and how many is the count in each thoroughfare or locality, as shown in the final output, above.
View 3 Replies
View Related
Sep 11, 2012
I load a table through sql loader which takes nearly 14 min for 8-9 millions records, once the records complete i run the analyze table compute statics to gather stats and it takes nearly 15 min. is there any ways so that i can reduce the stats timing. the stats collection command runs from other schema not from where the table is residing.
View 1 Replies
View Related
Nov 16, 2009
When we execute select count(*) from table_name it returns the number of rows.
What does count(1) do? What does 1 signifies over here? Is this same as count(*) as it gives the same result on execution?
View 13 Replies
View Related
Nov 24, 2011
difference between count(1) and count(*). As i know count(*) will give number of rows irrespective of null and count(1) will not count the null.My Oracle version is 10 g.
SQL> select * from t1;
A B C
---------- -------------------- --------------------
1 2 3
2
5
SQL> select rownum,a.* from t1 a;
ROWNUM A B C
---------- ---------- -------------------- --------------------
1 1 2 3
2 2
3 5
4
[code]....
View 3 Replies
View Related
May 24, 2010
I have the folloiwng two queries:
Query_1: select count(*) yy from table1;
Query_2: select count(*) zz from table2;
I need to compute the following:
var:=(yy/zz)*100
How can I achieve this in a single query?
View 3 Replies
View Related
Mar 23, 2013
I'm using this code, and it performs fine, but I'm wondering if there is a more elegant way to do it--maybe with "ROLLBACK". Basically (as you can see) I need to get a normal count for each group but also for each group take a percentage of the total count (so all groups pct adds up to 100 (oh yeah, don't test for zero below, but just a test... )
select
c.Event,
c.code,
count(1) as calls,
total.total_count,
count(1) / total.total_count * 100 as pct_of_total
from
table1 c
[Code]....
[Edit MC: add code tags, do it yourself next time]
View 4 Replies
View Related
Feb 28, 2013
I have one tbale like
TABLE A with columns bu_code and bu_cty
bu_code bu_cty
12 ES
12 ES
13 AP
13 AP
so now i want to know the count for bu_code
like my output shd be
bu_code(12) ---count is 2
bu_code(13)------count is 13
View 4 Replies
View Related
Mar 4, 2009
I have this query and I want to get the COUNT:
SELECT first_np AS n_p FROM dvc
UNION
SELECT second_np AS n_p FROM dvc
UNION
SELECT n_p FROM dc;
This returns one column which is the n_p; how do I get the count of n_p?
View 2 Replies
View Related
Oct 9, 2008
say there is astring "mumbojumbo "i need the count of given string in it
ex:when o is given count shuld be 2 when m is given count shuld be 3
is there any pre defined function for counting a given string ...
View 2 Replies
View Related
Mar 26, 2012
I have a table with the following fields :
RGS_CAN_PERIOD NUMBER(4)
RGS_ID_ELECTOR NUMBER(
FBD_NID VARCHAR2(14)
RGS_SURNAME VARCHAR2(40)
RGS_O_NAME VARCHAR2(40)
RGS_ALIAS VARCHAR2(30)
RGS_ADDR VARCHAR2(75)
RGS_ID_REG_AREA VARCHAR2(3)
[Code]..
I need a report as hereunder :
Reg. Area Age <=19 20 <= Age <= 24 25 <=Age <= 29 Total No. of Voters
xxxx 10 15 7 32
yyyy 5 7 3 15
I have work out a script but the age is not in a range
select *
FROM (select rgs_id_reg_area,
count(decode(fbd_age,19,fbd_age)) Age19,
count(decode(fbd_age,20,fbd_age)) Age20
FROM rubyvoterstat where vote ='Y'
GROUP by rgs_id_reg_area)
order by rgs_id_reg_area
View 1 Replies
View Related
Feb 14, 2012
I have the following table with insert below.
CREATE TABLE ATTENDANCE
(
"REG_NO" NUMBER(7,0),
"COURSE_SEQ_NO" NUMBER(4,0),
"SECTION" VARCHAR2(1 BYTE),
"SEMESTER_CODE" NUMBER(1,0),
[code]...
How can I have the following output?
course_seq_nosectiontotal_recordscount_1count_0
322X20137
I tried decode but it could not find its way
View 9 Replies
View Related
Mar 10, 2010
I am running this query but am not getting data that is correct.
SELECT a.prod_id, a.prod_name, a.artist_name, COUNT(*)
FROM po_my_purchase_tb a, cm_track_tb b
WHERE a.prod_id = b.prod_id and b.GNR_CD = 'GR000017' AND a.purchase_date > '10-FEB-10' AND ROWNUM<50
GROUP BY a.prod_id, a.prod_name, a.artist_name, a.buy_seq
ORDER BY COUNT(*) desc
View 8 Replies
View Related
Mar 7, 2013
I need suing group by for getting count.I have a table with columns below
SYS_AUDIT_IDSYS_AUDIT_PROG_IDPROG_FINDING_ID_COUNT
178921652
178921641
178921631
179321521
179321511
179321501
179321491
179321461
I want to count number of SYS_AUDIT_PROG_ID for each audit and count of PROG_FINDING_ID_COUNT
I want to get
1789 3 4
I tried this query but this is not working
[code]select sys_audit_id ,count (sys_audit_prog_id), count(prog_finding_id_count) from
my_table sub
group by sys_audit_id [/code]
View 2 Replies
View Related
Dec 13, 2012
I need to get an accumulated value for a count. E.g. The table has purchased date, purchased item, purchased item type. The count of purchased item groyup by purchased type on every purchased date. Now, we got the count value (purchased item). But, I want the accumulated count value on every purchased date. So that I will get that how many items has been purchased on a particular date.
View 5 Replies
View Related
Nov 16, 2011
I pulled in 1121 SSN's into a table and am using that table as the basis for returning data from other tables...including how many documents a user has in their folder.
My query; however, is only returning 655 rows...it is returning only those rows that have documents in their folders. I want to return ALL rows...WHETHER OR NOT THEY HAVE A DOCUMENT COUNT (count(*)). How can I get all 1,121 rows to return? I would like the output to look like:
SSN LOCATION EMP_STATUS FOL_STATUS COUNT(*)
-- For those folders containing documents:
XXX-XX-XXXX WHATEVER WHATEVER WHATEVER 12
-- For those folders containing 0 documents:
XXX-XX-XXXX WHATEVER WHATEVER WHATEVER NULL
Here is the query in it's current state:
-- Get User/Folder/Doc Count Information
SELECT b.ssn,
b.location,
b.emp_status,
c.fol_status,
COUNT (*)
[Code]....
So again, my problem here is that...not all FOLDERS contain DOCUMENTS...but I still want the folder data lised...I just need it listed with either a zero count (0), or a NULL in the COUNT(*) column.
I'm trying the various joins, but none of them seem to be working.
I've tried the old 8i (+) join as follows:
AND c.fol_id = d.doc_fol_id(+)
I've tried the inner join:
AND c.fol_id(+) = d.doc_fol_id
...and I've tried the 9i method (left outer and full outer) using the following types of notations:
folder c full outer join documend d on c.fol_id = d.doc_fol_id
...so far, no luck. I'm still having only 655 rows returned (the 655 are those folders that HAVE document count > 0. Any folder that has zero documents in the document table just aren't being returned in the query.)
View 9 Replies
View Related
Jun 14, 2007
we're having a few tables which queries about 10.000 articles. As we don't show them all at once we are using pagination and use the rownum to show only a limited number of the results.
Now as these queries are pretty complex we have to optimize them and since we use pagination we have to call our query twice (first we make a count(*) and then we call the small resultset of a few rows). Ofcourse we are looking for a solution to call it only once and still use the pagination. We could load the whole resultset of 10.000 results and let java show only a few but that makes our line between the oracle and webserver pretty heavy. Is there a way to call the total number of results and give back only a small resultset just in one query?
View 1 Replies
View Related
Sep 24, 2012
I have a table employee
name title
A manager
B sales
c manager
D engineer
E marketing
I want to know how many people has title as manager?
the answer is 2 how to do the count, I can't do count(title), right ?
View 2 Replies
View Related
Jul 13, 2011
SQL> select * from test11;
A B C
--------------------------------------
AA 123
BB 100 200
CC 300
AA
How to count no null in the above table
View 9 Replies
View Related
May 3, 2013
oracle SQL,
Student(unique) has five races in a table
I need a select statement with student_id - 1 column
I need to count the number columns with values 'Yes' in them
the columns are race1,race2,race3,race4,race5 for a single row --- this should be 2 ndcolumn
The 3rd column should contain the max of the race having 'yes' in them
i.e if race1 and race 3 have values'yes' in them i need 3 in the next column, - 3rd column
View 5 Replies
View Related
Jan 8, 2013
i was just looking to find out how it is possible to select * from a sub select that has data coming from 2 tables.
For example:
select *, count(*) AS Count
from
(
select ....does not work, but i am not sure how i can select all fields from the sub query.
View 3 Replies
View Related
