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;
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.
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
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.
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?
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.
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,
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)
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.
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.
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]....
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;
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...
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.
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.
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?
