Sql Summing Figures From Different Columns
Aug 21, 2007How do I make a select statement which will add up numbers from different columns or add up sums from different columns?
View 1 Replies
ADVERTISEMENT
SQL & PL/SQL :: De-Aggregate Year To Date Figures?
Apr 27, 2011I have a table as below:
Year Month Value
2011 01 15
2011 02 26
2011 03 34
[code]...
The value is an aggregate Year to Date Figure And I was wondering what the best method of splitting this data out into a Monthly Figure so that it would look like below:
Year Month Mth Value
2011 01 15
2011 02 11
2011 03 8
2011 04 9
[code]...
Summing Column - Adding Values Of Attribute
Apr 25, 2013i have 2 data block (maintenance & maintenanceparts) in one form and i will like to add the values of the attribute "LineCost" in the maintenanceparts datablock and put the sum into 'Totalcost' attribute in the maintenance datablock.
I tried use the code
"begin
select sum(maintenanceparts.LineCost)into :maintenance.totalcost
from MaintenanceParts;
end;"
But it ended up adding all the records that was save in that attribute and putting it in totalcost. I am new to oracle
Application Express :: Dynamically Update A Number Field By Summing Of Page Item Values
Jun 28, 2012I'm trying to update a number field on an apex page by summing up the values of multiple page items. I've tried to follow this tutorial
[URL]........
especially the "Create a Set Value Dynamic Action Using PL/SQL" part.
In the tutorial example they return an page item value P3_SAL multiplied by a multiplier they've determined by another page item's case. What I want to do is much simpler in that I just want to add up multiple page item values and then display them in a number field at the bottom of the page.
so the code I tried to use based on the tutorial is in the tab: Home >Application Builder>Application 103>Page 3>Edit Dynamic Action>Create / Edit Action
Set Type: PL/SQL Function Body
PL/SQL Function Body:
BEGIN
return :P3_ITEM1+P3_ITEM2+P3_ITEM3;
END;
Page Items to Submit: P3_ITEM1,P3_ITEM2,P3_ITEM3
But it gives me an error that I need to declare the identifiers, yet it works if I only use one item and I can perform any arithmetic.
i.e.
BEGIN
return :P3_ITEM1+1000;
END;
Reports & Discoverer :: Create A Report By Using One Field / Text As Columns Name In Layout But Display All Columns
Jun 16, 2010I want to create a report by using one field and one text as columns name in layout but display the all the columns. I mention the 5 column names in query.how can I write function in summary column.
View 4 Replies View RelatedPerformance Tuning :: Index 15 Columns When Cannot Predict Columns Will Be Used In Where Clause?
Apr 4, 2011I am running a fairly busy Oracle 10gR2 DB, one of the tables has about 120 columns and this table receives on average 1500 insertions per second. The table is partitioned and the partitioning is based on the most important of the two timestamp columns. There are two timestamps, they hold different times.
Out of these 120 columns, about 15 need to be indexed. Out of the 15 two of them are timestamp, at least one of these two timestamp columns is always in the where clause the queries.
Now the challenge is, the queries we run can have any combination of the 13 other columns + one timestamp. In reality the queries never have more than 7 or 8 columns in the where clause but even if we had only 4 columns in the where clause we would still have the same problem.
So if I create one concatenated index for all these columns it will not be very efficient because after the 4th or 5th column the sorting would no longer be very useful and I believe the optimiser would simply not use the rest of the index. So queries that use the leading columns of the index in sequence work well, but if I need to query the 10th column the I have performance issues.
Now, if I create multiple single column indexes oracle will have to work a lot harder to maintain all these indexes and it will create performance issues (I have tried that). Besides, if I have multiple single column indexes the optimiser will do nested loops twice or three times and will hit only the first few columns of the where clause so I think it will kind of be the same as the long concatenated index.
What I am trying to do is exactly what the Bitmap index would do, it would be very good if I could use the AND condition that a Bitmap index uses. This way I could have N number of single column indexes which the optimiser could pick from and serve the query with exactly the ones it needs. But unfortunately using the Bitmap index here is not an option given the large amount of inserts that I get on this table.
I have been looking for alternatives, I have considered creating multiple shorter concatenated indexes but this still would not address the issue since many queries would still not be served properly and therefore would take a very long time to complete.
What I had in mind would be some sort of multidimensional index, I am not even sure if such thing exists. But essentially it would be some sort of index that could serve a query efficiently regardless of the fact that the where clause has the 1st, 3rd and last columns of the index.
So considering how widely used Oracle is and how many super large databases there are out there, this problem must be common.
SQL & PL/SQL :: How To Select All Columns From Table Except Those Columns Which Type In Query
Jan 21, 2011I have a two question.
Question 1:How to select all columns from table except those columns which i type in query
Question 2:How to select all columns from table where all columns are not null without type each column name which is in empty data
SQL & PL/SQL :: Function In Oracle To Select Not-null Columns At Beginning And Null Columns At End?
Jul 12, 2012I have 8 columns. Some of them might be null.I want to display all 8 columns in my result. Not null columns will be first and null at the end.Here is a sample data :
Employee table :
Employee_id Emp_fname emp_lname emp_mname dept salary emp_height emp_weight
1 aaa ddd d1 100 6 180
2 bbb ccc 120 169
3 dfe d2 5.9 223
The expected result is :
result1 result2 result3 result4 result5 result6 result7 result8
1 aaa ddd d1 100 6 180
2 bbb ccc 120 169
3 dfe d2 5.9 223
PL/SQL :: How To Transpose A Table From Rows To Columns Or Columns Into Rows
Aug 22, 2012what are the collections available in Oracle Plsql, what are concepts of collection.
How to Transpose a Table from rows to columns or columns into rows.
DDL and DML concepts.
What is the concepts of statistics in Oracle Plsql.
SQL & PL/SQL :: Transpose Rows-to-columns And Columns-to-rows
Oct 6, 2010I need to transpose the following table columns to rows and rows to columns...Im not quite sure how to acheive this...I have the following table with fixed number of columns and dynamic number of rows based on date filter in query
MONTH_YEAR RMS RMS_OCC TTL_RMS
---------------------------------------
SEPTEMBER 200917790017790
OCTOBER 2009183831278818347
NOVEMBER 2009177901460517762
and I need to display this as
COL1 SEPTEMBER 2009 OCTOBER 2009 NOVEMBER 2009
--------------------------------------------------------------
RMS 17790 18383 17790
RMS_OCC 0 12788 14605
TTL_RMS 17790 18347 17762
Add Two Columns Together In SQL
Mar 13, 2010My database has three tables: members (with member name, member number and club number), clubs (with club name and club number) and matches (with win member number, lose member number and score).
I have a query which displays total number of matches won by a club, and number of matches lost by a club:
Quote: select c.clubname,
(select count (*) from members wm, matches w where wm.memberno = w.winmember and wm.clubno = c.clubno) WINS,
(select count (*) from members lm, matches l where lm.memberno = l.losemember and lm.clubno = c.clubno) LOSSES
from clubs c
order by WINS desc;
How can I modify this to add the WINS and LOSSES columns together in a new column? I have modified the query as such:
select c.clubname,
(select count (*) from members wm, matches w where wm.memberno = w.winmember and wm.clubno = c.clubno) WINS,
(select count (*) from members lm, matches l where lm.memberno = l.losemember and lm.clubno = c.clubno) LOSSES,
(select count (*) from members wm, matches w where wm.memberno = w.winmember and wm.clubno = c.clubno) +
(select count (*) from members lm, matches l where lm.memberno = l.losemember and lm.clubno = c.clubno) PLAYED,
from clubs c
order by WINS desc;
DEFAULT Value On Columns?
Mar 24, 2011I wanted to know the consequences of adding a DEFAULT value to an existing column in a table.I understand that when you add a DEFAULT value to a column which is Nullable, Oracle updates all the null values for the column to the DEFAULT value, generating a lot of undo/redo data.
Is adding a DEFAULT value to a NOT NULL column a problem? As the column is NOT NULL, an update would not be done, so no undo/redo data will be generated.But will this cause a whole table scan? Is this advisable?
SQL & PL/SQL :: Specific Columns In For All
Jun 9, 2010I want to insert only specific number of columns into a table by using Bulk collect and Forall.
SQL> create or replace procedure bifa_proc
2 is
3 type etab is table of emp%rowtype index by binary_integer;
4 erec etab;
5 cursor c is select * from emp;
6 begin
7 open c;
[code]...
Warning: Procedure created with compilation errors.
PLS-00382: expression is of wrong type
PLS-00436: implementation restriction: cannot reference fields of
BULK In-BIND table of records
How to insert specific number of columns without declaring multiple table type definitions for each column by using bulk collect and forall.
SQL & PL/SQL :: Concatenation On Columns
Feb 23, 2012I have problem with query ( i'll only use DUAL table for example):
SELECT T.DUMMY P1,
T.DUMMY P2,
T.DUMMY C1,
T.DUMMY C2,
T.DUMMY C3,
T.DUMMY C4,
T.DUMMY C5
FROM DUAL T;
I need to concatenate only columns with name starting with C and add separator between them. Is there any function do concatenate columns using separator like:
SELECT T.DUMMY P1,
T.DUMMY P2,
DBMS_SOMETHING.SOME_FUNCTION(',', C1,C2,C3,C4,C5)
FROM DUAL T;
Or I must do like this:
SELECT T.DUMMY P1,
T.DUMMY P2,
C1 || ',' || C2 || ',' || C3 || ',' || C4 || ',' || C5
FROM DUAL T;
problem is that I have 3.000+ columns in 23 different selects :/
SQL & PL/SQL :: Count In Columns?
Feb 14, 2012I 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
SQL & PL/SQL :: Display In Two Different Columns
Feb 1, 2013Below is returning rows in single column.I need to display in two different columns
SELECT * FROM TABLE(PLUSER.SPLIT('a,b,c'))
union all
SELECT * FROM TABLE(PLUSER.SPLIT('1,2,3'))
this returning result like
a
b
c
1
2
3
but need abc in one column and 1,2 3 in one column
a 1
b 2
c 3
i was tried like
select * from TABLE(PLUSER.SPLIT('1,2,3')),(select * from TABLE(PLUSER.SPLIT('a,b,c')));
but it's giving cartesion result like below
1a
1b
1c
2a
2b
2c
3a
3b
3c
SQL & PL/SQL :: How Many Columns Will Have Query
Apr 29, 2012I'm a beginner in PL/SQL ! --> " get_sql_metadata(p_query IN Varchar2) RETURN VARCHAR2;" I have to display the names and columns of the query by using the package dbms_sql and how can I know how many columns will have my query.
View 4 Replies View RelatedSQL & PL/SQL :: From Columns To A Single Row
Sep 22, 2011I need to make the following transposition:
from this:
CUST_IDPLAN_IDNOREM_M4M ADD_M4M
6871 1231Yes Yes
68711232Yes NULL
into this:
CUST_ID PLAN_ID REM_M4M1 REM_M4M2 ADD_M4M1 ADD_M4M2
6871 123 Yes Yes Yes NULL
Aggregate Two Columns
Oct 25, 2011I have two table and I want to merge them
TERMS_TABLE
ID | TERMS
309 | 'hardware'
309 | 'software'
309 | 'computer'
TFIDF_TABLE
ID | TERMS
309 |'computer,phone,mp3....'
Now I want to add TERMS column of TERMS_TABLE to terms column of TFIDF_TABLE but If TFIDF_TABLE already contains TERMS of TERMS_TABLE then I should not insert this term to the NEW_TFIDF_TABLE , like that
result should be:
NEW_TFIDF_TABLE
ID | TERMS
309 |'computer,phone,mp3....,hardware,software'
How can I do that ?
SQL & PL/SQL :: More Columns Into One Column?
Aug 10, 2010I just ponder how to write SQL or PLSQ if I'd like to get more columns into the one column.
For example:
I have table T1 where are 10 records. One columns TEXT is VARCHAR2. And I need to get by SQL/PLSQL one record with the new column VARCHAR2 where will be written 3 records as I wrote.
select TEXT from T1:
result: aa
bb
cc
And the desire result should lool like:
aa bb cc
how to quickly do it?
SQL & PL/SQL :: Other Columns When Using Group By
Feb 18, 2011I have a table OS_CURRENTSTEP , and OS_WFENTRY
CREATE TABLE OS_CURRENTSTEP
(
ID NUMBER,
ENTRY_ID NUMBER NOT NULL,
STEP_ID INTEGER NOT NULL,
ACTION_ID INTEGER,
OWNER VARCHAR2(20 BYTE),
START_DATE DATE,
[Code]...
I need count of step_id from os_currentstep wh
here is the query
select count(step_id), step_id from os_currentstep where owner='Marty' group by step_id
this gives me the count I also need the name associated with this step_id from table OS_WFENTRY
, I cannot query name from step_id ,
SQL & PL/SQL :: Rows To Columns?
Apr 5, 2012Sample data
col1 col2 col3
1 A someval1
2 A someval2
3 A someval3
2 B someval4
3 B someval5
In col1 there will be always 1 or 2 or 3 value not more than 3 I am using oracle 10g.Want the following output in a single query with using user defined function or stored proc
OUTPUT
newcol1 newcol2 newcol3
someval1 someval2 someval3
null someval4 someval5
SQL & PL/SQL :: How To Concatenate Three Columns Into One
Feb 21, 2012I'd like to concatenate my 3 columns into one. I have this text item(NAME) and i want my columns lname, fname and mname to be bound into name text item.
this is so far my code to do the concat:
SELECT lname
INTO :ADVISEMENT.name
FROM students
WHERE :ADVISEMENT.name = lname || ',' || fname || ',' || mname;
but it didn't appear at all.
PL/SQL :: Count Columns In A Row
May 3, 2013oracle 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
Compare Two Columns Relationship
Apr 29, 2011I've a table (RelationshipX) with two columns with following values. The table represents the relationships. When I run the following query, It will give me all the combination of relationships...however, I need to get 15 unique as defined below.
Col1Col2
1106011060
1106011640
1106011142
1106011095
1106013029
1106014058
I run the following query to get below of all the combinaiton (note, I am opting out those six rows which matches to each other) select a.Col2 as Col1, b.Col2 as Col2 from RelationshipX a, RelationshipX b
where a.Col1 = 11060
and a.Col1=b.Col1
and a.Col2 <> b.Col2
order by Col1, Col2
[code]....
HOW can I modify my SQl so I get only 15 unique relationship records. (For example two UNDERLINE rows are technically same, and there are total 15 of them)???.
Converting Rows To Columns Using OWB
Aug 22, 2013Getting error ORA-00932: inconsistent datatypes: expected NUMBER got CHAR Source row:
NOTE_IDCONTRACT_GRANT_IDPROSPECT_IDPROGRAM_CODE
1 1 1 786
2 2 2 786
Program:
SELECT
CASE
WHEN "PIVOT_ROW_GENERATOR"."ID" = 0 THEN
"PIVOT_SOURCE"."ID_NUMBER"
WHEN "PIVOT_ROW_GENERATOR"."ID" = 1 THEN
"PIVOT_SOURCE"."ID_NUMBER"
[code].........
Append Columns From One Table To Another
Nov 14, 2011Perhaps this is a common request : I have 2 tables:
Table A
-------
ID Value
1 a
2 b
3 c
Table B
-------
ID AnotherValue
1 x
2 y
I am hoping to append a column from Table B to Table A based on a simple sql join (e.g:
Table A
ID Value AnotherValue
1 a x
2 b y
3 c (null)
)
I would rather stay away from the standard update statement since it takes far to long and I'd prefer not to use create table as I don't want to duplicate any data...is this possible to do ? (e.g: just insert the columns into this table ?) - or if it's possible the performance overhead just wouldn't make it worth it ?
Joining Two Columns In Same Table?
Jan 30, 2007We have a table for reports. If user A submits a report ...and say the sequence # is 242. When this report goes to Admin ...he submits this request then in same table we add another row with say sequence # 245. THEN we update column called 'Asctd ID' for 242 and add 245 in there. and then update Asctd ID for 245 and add 242 in there.
(This table has many fields, one of which is report Name field)
Now i am running a query like this...
SELECT b.JOB_ID, a.DESC, TO_CHAR(a.CREATE_DATE,'MM/DD/YYYY'),
DECODE(a.DLVRY_TYPE,'','PDF',a.DLVRY_TYPE), DECODE(a.FLG,'','N/A',
a.FLG), ((TO_DATE(a.CREATE_DATE,'DD-MON-YY')) + 21) -
TO_DATE(SYSDATE, 'DD-MON-YY'), c.STATUS_DESC, a.SIZE_NUM,
b.PRVDR_ID, a.asctd_id, d.NM
FROM REQUEST_DIM a, PROVIDER_DIM b, STATUS_DIM c, DIM d
WHERE a.FLAG = 'P' and RTRIM(a.RPT_RQST_USER_ID) = 'TEST02' AND a.RPT_RQST_TYPE = 'D'
AND a.RPT_RQST_ACTIVE_IND = 'A' and a.asctd_id is not null and a.RPT_RQST_ID = b.RPT_RQST_ID
and b.JOB_ID = c.JOB_ID and b.PRVDR_ID = d.PRVDR_ID
AND c.CREATE_DT = (SELECT MAX(d.CREATE_DT)
FROM STATUS_DIM d
WHERE d.RPT_RQST_ID = b.RPT_RQST_ID and d.JOB_ID = b.JOB_ID )
ORDER BY a.RPT_RQST_ID, a.CREATE_DATE
Now this query is run by the admin (job 245) ...it returns a bunch of stuff and also the name of the report that the admin gave this. But when admin sees this we want to be able to displace the report name that user A gave it (asctd ID 242). so the row 245 HAS a asctd ID 242. and there is a row 242 from which we can get the name easily. But i dont know if this is possible in ONE QUERY??
SQL That Treats Rows As Columns
Oct 29, 2008Note: This is not a homework assignment, but rather, a technical bottleneck at work.
My dilemma is such: let's say that I have 1 teacher in an entity, and this teacher has 3 students in an associative entity. So if you did a select T.teacher, S.student from TEACHER T, STUDENT S, where T.teacher_id = S.teacher_id, you would get 3 rows:
Teacher 1, Student 1
Teacher 1, Student 2
Teacher 1, Student 3
How I would like to display it, is in 1 row:
Teacher 1, student 1/student 2/student 3
Is there a way to write a SQL to achieve the above?
Rows Into Columns Like Pivot
Sep 5, 2011I have this following data in a single table
Student
Semester
Subject
Marks
and I want to achieve the following.
I am asked to write a query with the students as Rows with thier subjects and marks as per thier semester which is the columns.
new to this type of queries...This is somewhat like pivot..
Those who have not appeared for a semester should be null just exactly as shown above.Is it posible ?
