My goal is to have a de-normalized summary table in which total communication volume for each distinct channel is displayed per customer.Briefly, I would like to reach the following output
When I run the sql below I am not able to de-normalize by customer id; counts are accurate.
SELECT distinct cil.customer_id, (CASE channel WHEN 'SMS' THEN (SELECT COUNT (channel) FROM customer_interaction_log cil1 where cil1.channel='SMS' and cil1.customer_id=cil.customer_id) END) SMS_COUNT, (CASE channel WHEN 'Email' THEN (SELECT COUNT (channel) FROM customer_interaction_log cil2 where cil2.channel='Email' and cil2.customer_id=cil.customer_id) END) EMAIL_COUNT FROM customer_interaction_log cil;
select partition_name, num_rows from dba_tab_partitions where table_name='Partisi_Book_Issue';
it display 0 num_rows. But i have made non partitioned table to partitioned table with the same number of rows. how to display number of record in each partition in any table?
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 have a select statement that selects all columns from the join of 3 oracle views. I would like to change it to select only the distinct rows, not sure how to code this. Here is my sql statement:
select * from myschema.view_1 acct Left JOIN myschema.view_2 freq
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 need to get the number of rows based on one condition for certain time period. For example, writing a query for 3(any) hrs , i need to get the output as count of rows as hourly basis. Like these:
Date Time Count 12/08/2010 16:00 234 12/08/2010 17:00 345 12/08/2010 18:00 645
I want to create a SELECT, that shall give back only a special amount of rows, depending on the sum of one of the selected fields.
At first a code sample of the complete selection:
SELECT DISTINCT mnr, ktxt, (SELECT Sum(meng_4)FROM reldb d1 WHERE d1.mnr=d.mnr)qty FROM reldb d WHERE mnr IN (SELECT mnr FROM relac WHERE Lower(rlnr) NOT LIKE 'platte geprägt%') AND saext='M' ORDER BY qty DESC,ktxt;
This selection produces some lines of output (in my case i.e. like 300). What I want to see is only that much lines that the condition 'sum of all items listed below meng_4<=sum of all items meng_4 of the whole selection * 0.9' is fulfilled.
So, if the whole selection produces a total of 10000 as sum for all items meng_4, I want to see only that amount of rows that sums a total of at least 9000 for all items meng_4. I hope, this specification is exactly enough to understand my intent.
insert into t_target (...) select (fiels,fields,fiels) from sourcetable; commit;
i want to capture number of rows committed and assign it to a variable or insert it into a table, i can not rely on count of source table or t_target because both those tables are changing through out of the day/ data are being inserted and removed from those tables. is this possible
I have a I/R report with a checkbox is column 1 of each row using:
HTMLDB_ITEM.CHECKBOX (1,R.RESEARCH_ID, decode(:Pxx_SELECT_ALL_ROWS,'Y','CHECKED', decode(instr(nvl(:Pxx_SELECTED_IDS,0),R.RESEARCH_ID),0,NULL,'CHECKED')) ) AS select_flag
I want to use Javscript or a Dynamic Action to "count" how many selected rows there are, when a UPDATE button is pressed. Right now, the UPDATE button "submits" the page, then a page item computation runs the following code to get a count:
DECLARE v_cnt number(9); BEGIN v_cnt := 0; FOR i in 1..htmldb_application.g_f01.count LOOP v_cnt := v_cnt + 1; END LOOP; return v_cnt;
This works... but I dont want to submit (and rerun the report) just to get this count.
I have copied this loop into a Dynamic Action using the Click action of the UPDATE button, but it doesnt seem to recognize htmldb_application.g_f01.count .. so my value returns as 0.
I had to create a new column in a particular table now i want to insert the values in that column though the other columns are already populated I entered the command (insert into Product(STANDARD_PRICE) values(895.99) when i hit return it says cannot enter null value into (SYSTEM .PRODUCT. PRODUCT_ID) product_id is the PK which is the first column STANDARD_PRICE is the last column in my table...how do i enter the values into that column without receiving this error or having to effect the other columns?
select distinct c.process_id a.role_id,a.email_address,c.process_name from role a, notification_role b, process c where a.role_id=b.role_id and b.process_id = c.process_id
I want the number of UNITS with the Status EN/PL for AREA 3/6
ID 1 has area 3 and 6 but only EN so count 1 (no PL) ID 2 has area 6 but DR so no count ID 3 has area 3 and 6 for EN and PL so 1 for each ID 4 has area 6 three times and 3 once so count.
CREATE TABLE DAN_T1 ( ID varchar(8), AREA varchar(8), UNIT varchar(8), STATUS varchar(8) )
One of my colleaque gives the below informations. it displays table count information while gathering stats. But I dont know from which view he picked the historical information. pls let me know the view name to get this detail.
We have table, which maintain log record of gl table. I don't know how much data exist in that table but problem is taking too much time while counting whole records.
