I want to use Analytical function instead of group by clause for below query..
select
CASE
WHEN ADMT.SOURCESYSTEM ='CLU'
THEN COUNT(ADMT.TOTAL_COUNT)*5
ELSE COUNT(ADMT.TOTAL_COUNT)
END TOTAL_COUNT
from ESMARTABC.ABC_DRVR_MFAILS_TMP ADMT
group by ADMT.SOURCESYSTEM
I need to calculate the sum of values over a period of exactly one month (including the current row). Now if I use a windowing clause of "range between interval '1' month preceding and current row", the total period length is 1 month plus one day (being the day in the current record).
Basically, I want to sum over a period starting at "add_months(startdate, -1) + 1" up until startdate of each row.
drop table window_tst;
create table window_tst
( id number primary key
[Code]....
So instead of having 01-feb going back to 01-jan, it should only include 02-jan till 01-feb
I could of course recalculate the period length back to a number of days for each row, but that is not really what I would prefer, as it would make the code rather unreadable.
I have simplified this for ease of understanding. I have a Data column and a Month_ID column like this:
Values Month_ID
--------- -------------------------------------------------------
AAA 1
BBB 2
I split this out to values per year like this
Value_2011 Value_2012 Month_ID
-------------------------------------------------------------------------
AAA 1
BBB 2
Now i am trying to get the max(Value_2011) keep (dense_rank Last order by Month_ID) but i get a NULL. I can understand its because the Month_ID accomodates all years but i only need it to look at Month_ID for 2011 and return me the last dense_rank value, how can i achieve this?
I tried a couple of different methods like Last_Value() but i have group by in my original statement and i think analytical functions dont like GROUP by if they are not part of it. How can i achieve this?
I'm posting below test case in which I'm not able to understand output for LAST_VALUE function. I'm expecting maximum value for the salary in a department. Because I'm partitioning by department and ordering a partition as assending so being last value it should give me maximum value within a partition i.e. department in this case.
CREATE TABLE EMP_MST
(
EMP_ID NUMBER(5),
EMP_NAME VARCHAR2(30),
CONSTRAINT PK_EMP_MST PRIMARY KEY(EMP_ID)
[code]...
Here is the test-table creation script:
CREATE TABLE TEST1 (AGG_DATE DATE, COL1 NUMBER(9), COL2 NUMBER(9), COL3 NUMBER(9));
Here is the test-data population script:
insert into TEST1 (AGG_DATE, COL1, COL2, COL3)
values (to_date('01-01-2012', 'dd-mm-yyyy'), 1, 1, 1);
[code]....
The problem is when I wrote an analytical query, it is giving the BEGIN_DATE and END_DATE by taking all the partition values together and so instead of the values above, it is creating an answer as follows:
Wrong Dataset
BEGIN_DATEEND_DATECOL1COL2COL3
1/1/20121/8/2012111
1/1/20121/8/2012111
1/1/20121/8/2012111
1/1/20121/8/2012111
1/4/20121/11/2012222
1/4/20121/11/2012222
[code]....
Only the last row is correct. What can I do to get the right answer as I know am falling short? Here is my current query:
SELECT MIN(AGG_DATE) OVER(PARTITION BY COL1, COL2, COL3) BEGIN_DATE,
MAX(AGG_DATE) OVER(PARTITION BY COL1, COL2, COL3) END_DATE,
COL1,
COL2,
COL3
FROM TEST1;
can we use distinct keyword with the count and sum analytical functions?
View 5 Replies View Relatedhow to delete duplicated records from a table without using row_id. I found the duplicated rows from a table using Analytical Function. But i could not use the Analytical function in the where condition.
My table(tab2) Structure is
DEPTNODEPT_NAMEEMPIDSEXID1
107jadf 1F1
40asdf 55
10purchase 2M2
10sales 3M3
30HR 4F4
I found the Duplicate Record by using the query
with a as
(select deptno,dept_name,empid,sex,id1,row_number()over(partition by deptno order by deptno) rnum from tab2)
select * from a where rnum >1
how to delete duplicate record .
Is anyway to create function based index for group function columns.
For example
select max(timestamp),min(age),averge(sal).... ... .. from tab;
This time, I am going to provide the DML statements.
I have a simple table with 3 fields in it.I want to group by ACCT_NUMBER and sum of BALANCE field. But I want to get the description of the first row in a group.
the statements below. Here there are two groups of records 2001 and 2002. My sql(which I am working on) should return the following :
2001 EMPL TRAINING-MIS 20
2002 OTHER PROF SERV-HR 40
The following query will group by ACCT_NUMBER and sum of the BALANCE field. But how can I get the DESCRIPTION?
SELECT ACCT_NUMBER, SUM(BALANCE)
FROM TEST
GROUP BY ACCT_NUMBER
CREATE TABLE "TEST"
("ACCT_NUMBER" VARCHAR2(20 BYTE),
"DESCRIPTION" VARCHAR2(20 BYTE),
[code]...
getting expression into a Group By query in oracle.
I have a simple table with two columns. 'ID' and 'Amount'
I want output of the SQL to the following (only 2 fields in the output): I have attached the desired output.
select sum(amountheld) from table1
where member_status = 'MEMBER'
group by ID
This group by query works. But how can i get the expression field (the first field which 'TEMPACCOUNT') in this query (based on my attached output).
Can Group BY clause have an expression field?
Say for example, I want to group by the fields "Type" and and expression called "expression" (which is a case statement). I tried running this query and it says "expression" is invalide identifier.
select type, case when SUBSTR (glnumber, 1, 2) = '05' then 'IS'
when SUBSTR (glnumber, 1, 2) = '06' then 'IS'
else 'BS'
end "expression" , sum(balance)
from table
group by TYPE, "expression"
If there is any online material on how to GROUP BY on an expression(like above)
This query is taking 7 hours to execute as I am retrieving data from history table dept_hist.
select count(distinct empid), e.group_nm, d.date,
from emp e, dept_hist d
where e.deptno = d.deptno
and e.up_ts > sysdate -30
[Code]...
Its taking 7 hours to execute.restructing this query.
I have two columns Department and EmpName:
Department EmpName
____________________________________
Sales empname1
Sales empname2
Marketing empname3
Development empname4
Now I want to count the number of employees in every department. I want the output to be
Department Total
______________________
Sales 2
Marketing 1
Development 1
I am retrieving names of the department through a subquery
The query I am trying to execute is:
SELECT Department, Employee FROM
( SELECT ...query from other table) AS Department, count( A.EmpName) AS Employee
FROM Employer A, EmployeeInfo B
WHERE (A.EmpID = B.EmpID AND A.EmpCategory like 'Category2')
GROUP BY Department
I know that you cannot group by using aliases and hence a little work around, but still the query isn't working.
Can we use NVL Function in a from clause?
SELECT t1, t2, nvl(t3,0) t3 FROM
(select nvl(t1,0), nvl(t2,0),nvl(t3,0) from table1).........
can we NVL here?
Can we call a function in IN of "WHERE" clause.I mean to say like:
Select *
FROM table1,table2
WHERE table1.col=table2.col and CONDITION IN FUNCTION1
Am running the below query and getting the error below :
ORA-00934: group function is not allowed here
SELECT CONTRNO,
SUBNO AS USER_CODE,
TOTAL_POINTS AS AMOUNT,
STARTDATE + 30 AS DATE_F,
'SUB' USER_TYPE
[Code]....
IN clause is not working for stored function.At same time, the LIKE conditon is working.
SQL> CREATE OR REPLACE FUNCTION GET_EMPLOYEES (in_asset_type in SECURITY_TYPE.asset_type%TYPE)
2 RETURN VARCHAR2
[Code].....
i am having two sql query which i have made union of it but i need group by common.
select
distinct ac.CUSTOMER_NAME cust_name,
ac.CUSTOMER_NUMBER cust_no,
ps.CUSTOMER_SITE_USE_ID,
raa_ship_ps.PARTY_SITE_NAME loc,
sum(days_30.AMOUNT_DUE_REMAINING) D_30,
[code]........
I am trying to use decode funtion with GROUP BY ROLLUP.
MY query is working fine when i use this two queris individually
SELECT SUM(SAL),DEPTNO,JOB FROM EMP GROUP BY ROLLUP ((DEPTNO),(DEPTNO,JOB));
SELECT SUM(SAL),DEPTNO,JOB FROM EMP GROUP BY ROLLUP((JOB),(DEPTNO,JOB));
But when i use Decode funtion so that i can combine above two scenarios it is not working
SELECT SUM(SAL),DEPTNO,JOB FROM EMP GROUP BY ROLLUP ( DECODE(:A,'S',((DEPTNO),(DEPTNO,JOB) ),((JOB),(DEPTNO,JOB) ) ) )
I have a table like below:
COLUMN TYPE
USER_ID VARCHAR2 (10 Byte)
PROCESS_ID VARCHAR2 (30 Byte)
END_TIME DATE(STAMP)
TO_LOC VARCHAR2 (12 Byte)
TO_LOC_TYPE VARCHAR2 (15 Byte)
FROM_LOC VARCHAR2 (12 Byte)
ITEM_ID VARCHAR2 (25 Byte)
CASES NUMBER (12,4)
LMS_UDA1 VARCHAR2 (250 Byte)
ZONE VARCHAR2 (2 Byte)
I only want get one record with all columns, only have one clause MAX(END_TIME) But the other column have difference value. when i use MAX(END_TIME) and GROUP BY USER_ID,PROCESS_ID,CASES,... the sql didnot give one record, It give many records
I have a function that returns the total sum of an account. From reports I call the function passing the account code. The function sums the values for that specific account code and returns the value. In my function I have the following code :
where account_code = P_CODE.
Eg. The value of :P_CODE is 'CS'.
I now want to pass multiple account codes ('CS','TV',LJ') to the function. How do I change the IN clause in the function to accommodate multiple values.
I have tried using the instr function, but it does not work. eg. AND instr(o.ACCOUNT_CODES,','||P_CODE||',') > 0
DECLARE
cnt number(10);
BEGIN
SELECT COUNT(*) INTO CNT FROM TBL_ADDRESS WHERE ADDRESS_ZIP
IN (SELECT * FROM TABLE(MY_PACK.STR2TBL('46227')));
DBMS_OUTPUT.PUT_LINE (cnt);
END;
MY_PACK.STR2TBL() is a function which takes '|' delimited string, extracts values and returns a table of zipcodes. The function works fine and returns 46227 but the count returned is 0 instead of 280(count returned by replacing inner select with '46227').
I am having the following pivot query but I am not able to run it is giving error msg
ORA-00937: not a single-group group function
SELECT DISTINCT
C.SHORT_NAME,
MAX (
COUNT (DECODE (TO_CHAR (a.reg_date_cur, 'MON'), 'JAN', A.CAR_NO))
[code]...
can i create the user-defined functions and use them in the FILTER clause in the sem_match function? there are some built-in functions for the FILTER clasue. however, only one function (DATATYPE(literal)) support for date/time in the built-in functions. i want to implement some user-defined funcitons in the FILTER clause which can check time intervals in ontology. there are some functions about valid time in the WorkSpace Manager such as WM_OVERLAPS, WM_CONTAINS,WM_MEETS, etc. so, can i write some functions using the these valid time functions in WM and use them in the FILTER clause?
View 2 Replies View Relatedi want to select columns of 3 tables in such a way that period column should be in the group by function.
create view allocated_budgets_detail as
select ba.ba_fin_year, ba.ba_start_date, ba.ba_end_date, ba.ba_rev_no,
bh.bh_budget_code,
bd.bd_period,
bb.bb_entered_amount
from budget_header bh, budget_allocation ba, budget_distribution bd, budget_balance bb
where bh.bh_budget_id = ba.ba_budget_id
and ba.ba_line_id = bd.bd_budget_line_id
and ba.ba_line_id = bb.bb_budget_line_id
group by bd.bd_period
I have a very simple table with 2 columsn. As_of_date is one of the column. This column is "Date" data type.
When I use distinct clause inside a to_char function it gives the following error:
ORA-00936: missing expression
00936. 00000 - "missing expression"
The Sql is
select to_char(distinct(as_of_date),'mm-dd-yyyy') from sales
I can't see any syntax error in the sql..but forsome reason, it doesn't work.
I have 2 tables used in this problem: ODETAILS and ORDERS.
ODETAILS has the following columns: ONO, PNO, QTY, COST
ORDERS has the following columns: ONO, CNO, ENO, RECEIVED, SHIPPED, ORDER_COST
UPDATE ORDERS
SET ORDER_COST= 1 * ( select SUM(
SELECT COST
FROM ODETAILS
WHERE ORDERS.PNO=ODETAILS.PNO
)
);
In ODETAILS there can be more than 1 row for 1 order. So I'm trying to add all the COSTs in ODETAILS.
I am running a GROUP BY query on a few columns of enumerated data like:
select count(*), Condition, Size
group by Condition, Size;
COUNT(*) CONDITION SIZE
-------- ---------- --------
3 MINT L
2 FAIR L
4 FAIR M
1 MINT S
Well, let's say I also have a timestamp field in the database. I cannot run a group by with that involved because the time is recorded to the milisec and is unique for every record. Instead, I want to include this in my group by function based on whether or not it is NULL.
For example:
COUNT(*) CONDITION SIZE SOLDDATE
-------- ---------- -------- ----------
3 MINT L ISNULL
2 FAIR L NOTNULL
2 FAIR M NOTNULL
2 FAIR M ISNULL
1 MINT S ISNULL
I was reading a tutorial for analytical function and i found something like this
sum(princial) keep(dense_rank first order by d_date) over partition by (userid, alias, sec_id, flow, p_date)
How to translate this into simple queries / subquery? i am aware that analytical function are faster but i would like to know how this can translate to using query without analytical function.
Is there any way to apply the restriction on analytical functions, just like WHERE and HAVING .AS we know that we can apply the restriction on table by using WHERE and grouping functions by using HAVING clause .
For Ex: Departments wise count including all employees record :
SQL> select count(*) over(partition by deptno) dept_Count, ce.*
2 from scott.emp ce
3 order by deptno, job;
DEPT_COUNT EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ----- ---------- --------- ----- ----------- --------- --------- ------
3 7934 MILLER CLERK 7782 1/23/1982 1300.00 10
3 7782 CLARK MANAGER 7839 6/9/1981 2450.00 10
3 7839 KING PRESIDENT 11/17/1981 5000.00 10
5 7788 SCOTT ANALYST 7566 4/19/1987 3000.00 20
[code]....