SQL & PL/SQL :: Getting DISTINCT Count From Two Different Columns?

Feb 1, 2012

I have following query which gives currency code from two different tables. I would like to get the distinct count of currency codes from these two different columns.

SELECT eb.person_seq_id, eb.bonus_amount, eb.currency_cd, ed.currency_cd_host
FROM fr_emp_bonuses eb, fr_emp_details ed, fr_periods p
WHERE eb.person_seq_id = ed.person_seq_id AND ed.period_seq_id = eb.period_seq_id
AND ed.period_seq_id = p.period_seq_id AND p.period_status = 'CURRENT'
AND eb.bonus_amount >= 0 AND eb.person_seq_id = 3525125;

This query gives following result

3525125240000USDINR
35251250 USDINR
352512560000 USDINR
352512550000 USDINR

There are two distinct currency codes (USD, INR) and total amount is 350000. So I am looking for a query to give me the following result

3525125350000 2

View 9 Replies


ADVERTISEMENT

How To Count Distinct Characters From A String

Sep 26, 2005

how to count different characters from a string ....

View 7 Replies View Related

SQL & PL/SQL :: Export Based On Count Distinct

Jun 7, 2013

Is there a way to export records based on a select distinct into different xls or csv files in oracle? I could be using SQL Developer.

The following will generate 74 unique values which each contain approx 3000 records.

select distinct(telespornr from vingelen2012

View 25 Replies View Related

SQL & PL/SQL :: Count Distinct Rows In Table

Feb 21, 2013

I think this is easier than I think but all I want to do is count the distinct rows in a table.

WITH data AS
(SELECT 1 id1, 1 id2, 111 val1
FROM dual
UNION ALL
SELECT 1, 2, 222
FROM dual
UNION ALL

[Code]..

seems to get what I want

ID1VAL1CNT1
111114
212224
321114
422224

but can the count be done in a stand alone query such as??

SELECT DISTINCT id1,
val1,
COUNT(*) over() cnt1,
COUNT(*) over(PARTITION BY id1) cnt2
FROM data
ORDER BY id1, val1

NOT what I want

ID1VAL1CNT1CNT2
1111152
2122252
3211153
4222253

View 5 Replies View Related

SQL & PL/SQL :: Value Distinct Count To Be Multiplied With Different Amount?

Oct 8, 2013

I have the below SQL code -

SELECT DISTINCT
opr, CAST (distinctcount AS REAL) * 0.02 AS total, distinctcount
FROM ( SELECT DISTINCT opr, COUNT (*) AS distinctcount
FROM out
WHERE code = '22'
AND timestamp LIKE '201303%'
GROUP BY opr)

I would like this value 'distinctcount' to be multiplied with different amount for period January till June and from June to December. For instance for period (January till June) to be multiplied with 0.02, for period (June to December) to be multiplied with 0.03.

View 12 Replies View Related

SQL & PL/SQL :: How To Get Distinct Count Of Records From CSV File

Mar 18, 2010

the below is the csv file data. 1st value is for transaction id, 2nd one is for order id and 3 rd one is category_id

"1","45678","a"
"2","45478","b"
"2","45278","b"
"3","45678","d"
"4","45278","e"

I am reading the above file and need to return the total no. of distinct transaction id from the file . How can i acheive this? In the above case distinct transaction id count is 4

View 15 Replies View Related

SELECT DISTINCT On Multiple Columns

Sep 17, 2010

I've read so many different pages on this topic but I can't seem to get my query the way it needs to be. Here's the query:

select admitnbr, lastname||', '||firstname||' '||finitial, hphone, mobile, wphone, med_rec, dob
from patients join schedule using (key_patien)
join adtmirro using (key_patien)
where appt_state = 'ON HOLD'

Because patients in my database can have multiple appointments "on hold" there are duplicates in the results. I only need 1 record per patient in order to forward this information into an automated dialer to contact that patient. I do NOT want to call the patient over and over again. Once will suffice. I'm trying to make a distinction on the column 'med_rec'. One row per 'med_rec' will be awesome but I can't find a way to create a distinct on that column.

View 3 Replies View Related

SQL & PL/SQL :: To Create Columns From Distinct Rows In Reporting

Feb 22, 2010

I have a table PRODUCT with following structure:

Productcode Productname Quantity
AF Ade Fgh 100
LO Ldo Ope 50
SK Ske Kro 47
....

There may be any number of records in the table. Now I need to print distinct products in different columns such as.

AF LO SK
100 50 47

There may be any number of products...I need to print all those products as different columns as shown above. How to write the Stored procedure for this?

View 2 Replies View Related

SQL & PL/SQL :: Counting Distinct Occurrences Of Data From Multiple Columns

Feb 4, 2013

CREATE TABLE FEB05
(NAME VARCHAR2(20),
CITY VARCHAR2(20))
INSERT INTO FEB05 VALUES ('AKON','CIT22')
INSERT INTO FEB05 VALUES ('MARIA','WOT101');
[code]......

I WANT A QUERY THAT WILL GIVE O/P AS TO GET DISTINCT COUNT OF NAME AND CITY COLUMN separately.

I.E. THE O/P SHOULD BE

Quote:Count(dist name) Count(dist city)
6 5

View 5 Replies View Related

SQL & PL/SQL :: How To Retrieve CLOB Data Using DISTINCT Keyword For Other Columns

Aug 2, 2012

I have one query regarding how to retrieve the CLOB data.

The requirement is something that in the select statement there are around 20+ columns which i need to retrieve from around 5 tables after joining.

Since the result set after joining also will get duplicate values i need to use distinct keyword to filter the resultset. But in the 20+ columns there are 2 CLOB data columns which i need to retrieve.

Whenever i use DISTINCT i'm getting ORA-00932: inconsistent datatypes: expected - got CLOB error. I know that DISTINCT keyword cannot be used for CLOB datatypes.

View 15 Replies View Related

SQL & PL/SQL :: Count In Columns?

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

PL/SQL :: Count Columns In A Row

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

PL/SQL :: Adding Columns To Count Contents Of Other?

Aug 31, 2013

how to incorporate counts into my current SQL code  that would count food items, and then car items for each person. Once I understand this concept I hope to apply it to my real database (in Oracle) where I am counting medications and problems for each patient.For example using my test data below, the my goal would be to have count columns show that Cathy had 3 food count and a 1 cars item (the count would need to ignore the null values in these  columns). I don't really care if the count value repeats in every row for that person.

 Select data1.Name, data1.Category,Data1.Results,Case When data1.Category IN ('food') then data1.results end FOOD,Case When data1.Category IN ('Cars')then data1.Results end CARSFROM(Select T1.name Name, null Category, null Results from a_Main T1, a_Food T2 Where T1.cpi_seq = T2.CPI_SEQUNION Select T1.name, 'food', T2.Food  from a_Main T1, a_Food T2 Where T1.cpi_seq = T2.CPI_SEQUNIONSelect T1.name, 'cars',  T3.Cars  from a_Main T1, a_Cars T3 Where T1.cpi_seq = T3.CPI_SEQ)data1where data1.Category is not nullorder by Name,

[code]....

View 2 Replies View Related

Group By Multiple Columns / Count And Then Find A Max

May 24, 2008

I have three tables,let's say

table stores
sid | store_name
1 | one
2 | two
3 | three

table products
pid | sid | p_name
1 | 2 | pone
2 | 2 | ptwo
3 | 3 | pthree

table sales
said | sid | pid
1 | 2 | 1
2 | 3 | 1
3 | 2 | 2
4 | 1 | 3
5 | 2 | 2
6 | 3 | 2
7 | 3 | 2

and i want display the product that sells best in every store. I try to group by multiple columns counting how many times each product was sold in every store, but don't know how to select the one which was best sold (maximal number of times)

View 5 Replies View Related

Retrieve Distinct Values From Clob Using Distinct Operator

May 27, 2013

i have a table with a clob column and i have 150 records i want retrieve distinct values from the clob using distinct operator on clob will not work

View 1 Replies View Related

Reports & Discoverer :: Create A Report By Using One Field / Text As Columns Name In Layout But Display All Columns

Jun 16, 2010

I 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 Related

Performance Tuning :: Index 15 Columns When Cannot Predict Columns Will Be Used In Where Clause?

Apr 4, 2011

I 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.

View 12 Replies View Related

SQL & PL/SQL :: Difference Between Count(*) And Count(1)?

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

SQL & PL/SQL :: Difference Between Count(1) And Count(*)

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

Distinct On Presorted SQL

Oct 7, 2010

I have a function which returns a preformatted SQL but with duplicates as follows

FSI
..FSIL
..FSIL
....IS123
....IS123
....IS345
....IS345
....IS547
....IS547
..FSIR
..FSIR
....IS98777
....IS98777
....IS34567
....IS34567
....IS67799
....IS67799

I have to eliminate the above result set without changing the order. a distinct on the function returns a jumbled result set.

View 1 Replies View Related

SQL & PL/SQL :: Distinct Value From VARRAY?

Feb 21, 2012

I need to take the distinct values from VARRAY.. I have wrote following simple example. But it does not work. how to get the distinct value from VARRAY.

declare
type t is varray(10) of varchar2(10);
t1 t;
type r is table of varchar2(10) index by binary_integer;
r1 r;
begin
t1 := t('A','B','A','B','A','B','C');
select distinct * into r1 from table(select * from t1);
END;

View 1 Replies View Related

SQL & PL/SQL :: How To Select All Columns From Table Except Those Columns Which Type In Query

Jan 21, 2011

I 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

View 5 Replies View Related

SQL & PL/SQL :: Select Just 1 Row For Each Distinct Column Value?

Feb 16, 2011

i need a Select * from tablename just 1 Row for each distinct ActionMode column value

CREATE TABLE INSTRUCTIONAUDITLOGSS
(
TNUM NUMBER(10) PK NOT NULL,
BATCHTNUM NUMBER(10),
ENTRYTYPE VARCHAR2(8 BYTE),
USERGROUP VARCHAR2(8 BYTE),
USERID VARCHAR2(8 BYTE),
PRODUCT VARCHAR2(8 BYTE),

[code]...

View 5 Replies View Related

SQL & PL/SQL :: Use Distinct For Multiple Fields?

Oct 18, 2011

i have a query in this way

select field1,field2,field3 from Table1
union
select field1,field2,field3 from table2

In the query from table2 i am getting duplicate rows, HOW can i retrieve only distinct rows...Using distinct keyword did not work...if i have to post create and insert statements for this one...

View 3 Replies View Related

SQL & PL/SQL :: Distinct Values In A Column?

Jul 18, 2013

I have a table with two columns, like:

123 xxx
456 xxx
789 yyy
987 yyy

And in the output I would like to have:

123 xxx
789 yyy

I tried with distinct and unique on the second column, but it doesn't work.

View 12 Replies View Related

SQL & PL/SQL :: Multi Column Distinct

Sep 16, 2010

I need to select multiple columns but only have 2 of them which are distinct. For instance if i have

userid lastname firstname city country time
1 jones tom lon gb 2:25
2 wall paul la usa 2:30
1 jones tom lon gb 2:50
3 smith jane ny usa 2:55

what i would want to do is select all the columns but avoid duplicate lastname-firstname combination rows. The problem is if i use a group by i have to include all the columns and because time is different i will get tom jones twice. a way of getting round this so i can select all the columns but only 1 row of tom jones.

View 7 Replies View Related

SQL & PL/SQL :: How To Retrieve Distinct Cat_id

Nov 1, 2011

Category Table
Name Null? Type
------------------------- -------- --------------
CAT_ID NOT NULL NUMBER
CAT_NAME VARCHAR2(20)

Qusestion Table
Name Null? Type
--------------------------- -------- -------
QUS_PK NOT NULL NUMBER
CREATED_DATE DATE
VIEW_COUNT NUMBER
CAT_ID NUMBER

select category.cat_id , qus_pk , cat_name , created_date , view_count
from category , qusestion
where category.cat_id = qusestion.cat_id
order by view_count desc

How can I retrieve distinct cat_id here?

View 21 Replies View Related

SQL & PL/SQL :: Distinct With Order By Clause?

Mar 13, 2012

I have got the following error while executing below Query.

ORA-01791 'Not a SELECTed expression'

select distinct sgbstdn_levl_code
from sgbstdn,spriden
where spriden_pidm = sgbstdn_pidm
and spriden_id = '200076543'
order by sgbstdn_term_code_eff desc;

The above Query is not working with Distinct & Order By clause are present and by joining two tables. I need the distinct values of levels in Descending order of Terms.

View 14 Replies View Related

SQL & PL/SQL :: Distinct With Orderby In Subquery

Mar 31, 2010

The table creation and Data insertion script is attached with the message.Basically I want to sort all the data based on the order by clause and then remove duplicates from the TSKID column and get distinct TSKIDs in the same order.I have below query to sort data:

SELECT *
FROM piwingetworkitems_vd
ORDER BY profilepriority,
authdptpriority,
returnpriority ASC,
priority DESC,
effdate,
tskid

But when I add a DISTINCT to the query, it does some kind of random sort and doesn't return the data as per above ORDER BY query and ignoring the SORT order.

SELECT DISTINCT tskid
FROM (SELECT *
FROM piwingetworkitems_vd
ORDER BY profilepriority,
authdptpriority,
returnpriority ASC,
priority DESC,
effdate,
tskid)

Is there any way to select the DISTINCT taskids ordered as per requirements?

View 13 Replies View Related

SQL & PL/SQL :: Distinct Timestamp - One Entry Per Day?

Apr 21, 2010

how can I distinct this query to get just one entry for one day.

this query:

select to_date(to_char(TIMESTAMP, 'YYYY-MON-DD HH24.MI.SS'), 'YYYY-MON-DD HH24.MI.SS') datum from event_table where id=15 ORDER by timestamp

returns:
Datum
01-MAY-09
01-MAY-09
01-MAY-09
..
..
..
02-MAY-09
02-MAY-09
.
.

but i want to have:

Datum
01-MAY-09
02-MAY-09
03-MAY-09
.
.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved