SQL & PL/SQL :: How To Get Total Number Of Occurrences Based On Value Of Column
Apr 20, 2013
I have a query that should return count the number of rows depending on the value of SLOT. The expected result will be like this:
WIPDATAVALUE SLOT N M
1-2 TRALTEST43S1 1 3
1-2 TRALTEST43S1 2 3
3 TRALTEST43S1 3 3
4-6 TRALTEST43S2 1 4
4-6 TRALTEST43S2 2 4
4-6 TRALTEST43S2 3 4
7 TRALTEST43S2 4 4
The M column is used to count the total number of occurrences of a SLOT. Now, as for the N field, this is used to count the occurrence of the SLOT. In my example for the SLOT TRALTEST43S1, it has three occurrences so M will be 3. Why 3, is because of the WIPDATAVALUE. The WIPDATAVALUE of TRALTEST43S1 is 1-2 and 3. 1-2 WIPDATAVALUE signifies two occurrences (one to two) and 3 signifies only one occurrence. As for N, it should just count the number of occurrence. To further explain, see below:
WIPDATAVALUE SLOT N M
1-2 TRALTEST43S1 1 3 -> First occurrence in the total of 3
1-2 TRALTEST43S1 2 3 -> Second occurrence in the total of 3
3 TRALTEST43S1 3 3 -> Third occurrence in the total of 3
4-6 TRALTEST43S2 1 4 -> First occurrence in the total of 4
4-6 TRALTEST43S2 2 4 -> Second occurrence in the total of 4
4-6 TRALTEST43S2 3 4 -> Third occurrence in the total of 4
7 TRALTEST43S12 4 4 -> Fourth occurrence in the total of 4
This is the query that I have so far:
SELECT DISTINCT
WIPDATAVALUE, SLOT
, LEVEL AS n
, m
FROM
(
SELECT
WIPDATAVALUE
, SLOT
, (dulo - una) + 1 AS m
[code]....
I think that my current query is basing its M and N results on WIPDATAVALUE and not the SLOT that is why I get the wrong output. I have also tried to use the WITH Statement and it works well but unfortunately, our system cant accept subquery factoring.
View 2 Replies
ADVERTISEMENT
Apr 20, 2013
here on your forum but has been following several threads ever since.So anyway here is the thing, I have a query that should return count the number of rows depending on the value of SLOT. Something like this:
WIPDATAVALUE SLOT N M
1-2 TRALTEST43S1 1 3
1-2 TRALTEST43S1 2 3
3 TRALTEST43S1 3 3
4-6 TRALTEST43S2 1 4
4-6 TRALTEST43S2 2 4
4-6 TRALTEST43S2 3 4
7 TRALTEST43S2 4 4-----
As you can see above, on the SLOT TRALTEST43S1, there are three occurrences so M (Total number of occurrences) should be three and that column N should count it. Same goes with the SLOT TRALTEST43S2. This is the query that I have so far:
SELECT DISTINCT
WIPDATAVALUE, SLOT
, LEVEL AS n
, m
FROM
(
[code]...
I think that my current query is basing its M and N results on WIPDATAVALUE and not the SLOT that is why I get the wrong output. I have also tried to use the WITH Statement and it works well but unfortunately, our system cant accept subquery factoring.
View 23 Replies
View Related
Apr 3, 2012
Table : Customer
Cust_idNameid
227ABC1
227ABC2
227ABC3
228XYZ1
228XYZ2
228XYZ3
228XYZ4
In one oracle table (Customer) we have multiple cust_id based on that, we want to populate id column as cust_id record count.
I have id column right now null and want result same as above.
View 1 Replies
View Related
Oct 15, 2013
I have one hirarchical query which return the parent to child hirarch level data. it has 11 level child data. i want to create column based on number of child in hirarchy. though i know it is 11 but it can change also.Is there any way i can create the column dynamically
ORG_UNITCOST_CENTERORG_UNIT_NAMEPARENT_ORG_UNITLLSYS_CONNECT_BY_PATH(ORG_UNIT,'/')
500171960000022000Managing Director - LUL500169965/00000001/50000001/50017588/50016996/50017196
500018370000021241FSO500171966/00000001/50000001/50017588/50016996/50017196/50001837
502894940000021241Knowledge Management500018377/00000001/50000001/50017588/50016996/50017196/50001837/50289494
508014980000021241Finance500018377/00000001/50000001/50017588/50016996/50017196/50001837/50801498
View 1 Replies
View Related
Nov 8, 2013
I have a table of documents (excel, word, pdf, txt) stored in blob column. I want that SCORE returns the number of occurrences within each document, so I'm using this piece of code:
SELECT bdt.*, SCORE(1) AS score_1, DBMS_CRYPTO.HASH(archivo, 3) AS cod_hashFROM crg_ctx_archivos_recibidos bdt
WHERE CONTAINS(archivo,'<query> <textquery lang="SPANISH" grammar="CONTEXT">DEFINESCORE(paz, OCCURRENCE * 0.1)</textquery> <score datatype="INTEGER" algorithm="COUNT"/></query>', 1) > 0
ORDER BY SCORE (1) DESC;
However I'm getting a maximum score of 10, but I know one of these documents have 490 matches. How can i get the exact number of matches per document? I'm using Oracle 11.2
View 3 Replies
View Related
Feb 4, 2012
The code which I am working on consists of an incoming dynamic string which be in the form of binary digits. The max size of the string will be 12 digits. For example, the string can be '111011000001', '000000000000', '111111011111', etc.
I need to find the number of occurences of '111' in the incoming string. Say in the 1st example, result will be 1, in the 2nd example result will be 0, and in the third example, the result will be 3.
I have been trying to capture the string length and replacing the variables '111' to find the number of occurences, but it isn't giving me the result that I want. This is what I have tried
SQL> conn hr/hr
Connected.
SQL> show user
USER is "HR"
[Code]....
I searched the forum and found a similar topic, and following that guideline, I even tried dividing the string with the length of the pattern. It works in some scenarios (the first and second examples mentioned below), while it fails in some scenarios (third example mentioned below)
SQL> select (length('11101110111') - length(replace('11101110111','111','')))/length('111') as occurences from dual;
OCCURENCES
----------
3
SQL> select (length('110111110111') - length(replace('110111110111','111','')))/length('111') as occurences from dual;
OCCURENCES
----------
2
SQL> select (length('111111111111') - length(replace('111111111111','111','')))/length('111') as occurences from dual;
OCCURENCES
----------
SQL>
View 13 Replies
View Related
Feb 17, 2011
My requirement is to concatenate two column values and place them in a new column.I have done it using self join but it limits the purpose,meaning when I have more than 2 values for grouped columns then it won't work.How to make this dynamic,so that for any number of columns grouped,I can concatenate.
SELECT a.co_nm, a.mnfst_nr, a.mnfst_qty,
a.mnfst_nr || ':' || a.mnfst_qty || ';' || b.mnfst_nr || ':'
|| b.mnfst_qty
FROM vw_acao_critical a JOIN vw_acao_critical b
ON a.co_nm = b.co_nm AND a.mnfst_nr = b.mnfst_nr
[code]......
What will be the case when I need to concatenate for more number of values.
like when co_nm has three bahs and manfst_nr and manfst_qty has 3 values for each for bah.and if three are having same_mnfst nr then I should use something dynamic.how to achieve this.
View 10 Replies
View Related
Jan 26, 2010
i am using oracle developer 6i report builder i required this type of query
example
if (:page number LIKE '1')
then
srw.set_text_color('darkred');
end if;
return (TRUE);
end;
but page number is not my table database item how can i use builtan page &<pagenumber> use for conditional format.
View 34 Replies
View Related
Jun 25, 2013
I have created a custom form which fetches certain records. There is a column called invoice_create_flag which is check box. By default when we query this form, value of check box is 'Y'. Another column is Amount.
Initially when form is called, all the records are selected. Hence it should show the total of all the records. Now If the users unchecks any record, System should deduct the amount of that particular record from the total amount.
Attached is form screenshot for reference.
View 1 Replies
View Related
Feb 19, 2013
I have a report created in Reports6i. I have two fields at the bottom
1.Page Total
2.Voucher Total
I do not want to print the Page Total if the total number of pages = 1.
How can I achieve this?
View 1 Replies
View Related
Apr 19, 2013
how to show total number of department with their department name assign to employee table.
View 2 Replies
View Related
Sep 17, 2010
DECLARE
l_query VARCHAR2(4000);
TYPE cursor_type IS REF CURSOR;
[Code].....
How can I get the total number of rows returned by the query?
I want to be able to check omething like c1.ROWS = 0
View 4 Replies
View Related
Jun 13, 2011
I want to know that whether we can write a query to add total number of count of each table.
for example:
select count(*) from emp;
count(*)
14
select count(*) from emp_1;
count(*)
15
select count(*) from emp_2;
count(*)
16
My question is that is it possible to add this all the three results and get the value as 45.
View 3 Replies
View Related
May 20, 2011
how to get total number of record in form
View 3 Replies
View Related
Sep 28, 2011
SELECT emp.emp_id, emp.ename, TRUNC (attendancedatetime),
TRUNC ( ( 86400
* (MAX (attendancedatetime) - MIN (attendancedatetime))
)
/ 60
)
- 60
[Code]...
Following is the out put:
EMP_IDENAME Date Min Hrs
10013Javed Iqbal09/20/2011 00:00:0036.007.00
10013Javed Iqbal09/21/2011 00:00:0027.007.00
10013Javed Iqbal09/22/2011 00:00:0049.007.00
10013Javed Iqbal09/23/2011 00:00:000.000.00
10013Javed Iqbal09/24/2011 00:00:000.000.00
i need the TOtal sum of Minutes and Hrs e.g 7+7+7=21 and also minutes but if minutes total increase from 60 minutes then it should add to hrs .how to get sum.
View 2 Replies
View Related
Jun 21, 2011
how to store total no of updated rows (number) in a variable after executing an updation query using script
View 2 Replies
View Related
May 21, 2011
I am working on form which consist of two block, now i need to know total record in detail block, but in form structure i have multiple details entry aginst 1 master entry and after going for next master entry the details of privious master entry are going for posting that's why i am unable to use the currnt_record function. function to retrive the total number of records in details tab.
View 2 Replies
View Related
May 4, 2012
is there any view in oracle for getting the total number of processes and currently used process in oracle? i am using oracle 11.2.0.1.0.
View 3 Replies
View Related
May 3, 2013
I have a multi record field of five rows. And 3 values.
the values are
IT
CSE
ECE
And i have one text item name is COUNT.
how to show the total number of records in Text item .
View 10 Replies
View Related
Apr 27, 2012
display the total number of employee working under president in emp table
View 5 Replies
View Related
Apr 14, 2009
I have following tables:
EMPLOYEE (E-Number, Name, Department, age)
ASSIGNMENT (E-Number, P-Number )
PROJECT( P-Number, Project, Manager)
Create a view to show employee names, age and total number of projects they are assigned to
View 2 Replies
View Related
May 15, 2011
i have two questions.
(1) how can i fill some value in a table column based on some existing column value automatically without user intervention. my actual problem is i have 'expiry date' column and 'status'. the 'status' column should get filled automatically based on the current system date. ex: if expiry date is '25-Apr-2011' and current date is '14-May-2011', then status should be filled as 'EXPIRED'
(2)hOw can i build 'select' query in a report (report 6i) so that it will show me list of items 'EXPIRED' or 'NOT EXPIRED' or both expired and not expired separately in a single report based on user choice. 'EXPIRED' & 'NOT EXPIRED' can be taken from the above question no. 1.
View 3 Replies
View Related
Sep 17, 2010
Below is the statement I am working with. When I get the result the under the Tester column it is a empty space after the total has been done. How can I add the word "Grand Total" under the tester column after the rollup.
SELECT BG_detected_by Tester, CONCAT(Round(count(bg_target_rcyc) / count(bg_status)* 100), '%') Percentage_Compeleted,count(BG_Target_RCYC) Total_Target_Cycle,count(bg_status) Total_Defects
From Bug
where BG_STATUS = 'Open'
GROUP by Rollup(BG_DETECTED_BY,BG_STATUS)
having (grouping(bg_detected_by) = grouping(bg_status))
Tester Percentage_completed Target Defects
empty space --->Grand Total
View 1 Replies
View Related
Sep 10, 2010
I am trying to create a trigger which changes the content of one column based on the information within another column.
For Example, if the 'ITEMQUANTITY' field drops below 1 then I want the STATUS column to say 'Out Of Stock'.
View 23 Replies
View Related
Nov 3, 2010
How can I Sort Ascending or Descending of Last Total Column of Matrix Report R6i i.e. F_SumsalPerempno, summing total salary of each empno at end of each row.
I need the employee paid highest amount of total salary during the year to appear on first row, while months to display as per original order.
View 2 Replies
View Related
May 23, 2013
In a classic report, I'm using the Sum functionality and breaks by First Column to get subtotals and report total. I Repeat Headers on Break which works great for the subtotals but I would like for the report to display the column headers above the report total for easier reading. If the last grouping has a lot of data, the user needs to scroll up to read the column headers when looking at the Grand Total.
Is there a way to Repeat Headers prior to report total?
View 3 Replies
View Related
May 27, 2013
I am trying to calculating the total column based on two columns, Total=unitprice*no.of items
I wrote this Javascript in page properties>javascrpt>Execute when Page Loads
"$('input[name=f05],input[name=f06]').change( function(){
parent_row = $(this).parents('tr:first');
sal = ( parent_row.find('input[name=f05]').val() == ' ') ? 0 : parseFloat(parent_row.find('input[name=f05]').val() );
comm = ( parent_row.find('input[name=f06]').val() == ' ') ? 0 : parseFloat(parent_row.find('input[name=f06]').val() );
total = sal * comm;
parent_row.find('input[name=f07]').val(total);
});
But its not Giving any result in total column?
View 3 Replies
View Related
Jan 1, 2013
Looks like I am at the end of my wits here but how to get the "DAY" based on current/given territory from a number between 1 and 7? Oracle has functions to extract day of the week but I can't find the other way round.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> alter session set nls_territory=AMERICA;
Session altered.
SQL> select to_char (sysdate, 'D') from dual;
T
-
3
SQL> alter session set nls_territory=GERMANY;
Session altered.
SQL> select to_char (sysdate, 'D') from dual;
T
-
2
I need a way to know that number "3" on database with NLS_TERRITORY = AMERICA means a "Tuesday" or number "2" on a database with NLS_TERRITORY=GERMANY means "Tuesday" as well.
View 9 Replies
View Related
Sep 28, 2012
my column type is NUMBER(10,0) ,it accept the input value from text field I using TO_NUMBER(?) to insert value into table, is the a way to handle if the input is 'aaaaaaaaaa' not digit?
View 6 Replies
View Related
Jun 28, 2013
i have two tables one as test_master and other one limit master.
test master table structure
ct_id ct_a ct_b ct_C ct_d ct_e...... etc
1 -- -- -- -- --
1 -- -- -- -- --
2 -- -- -- -- --
limit master structure
limit_id ct_a_limit ct_b_limit ct_c_limit ct_d_limit ct_e_limt ..... etc
1 -- -- -- -- -- -- -- --
2 -- -- -- -- -- -- -- --
ct_id and limit_id is match column used for mapping.
now i want the total count of no of records in test_master in which test_master column value not exceed limit master column limit value if any one column exceeds its limit then the same row will be in my count criteria.
how to achieve the same using sql/oracle query.
View 7 Replies
View Related