PL/SQL :: How To Group By Over A Range

Jun 12, 2012

I have a table like this:

x     y     AMNT
---------------------------------------
1     120     12
1     120     93
1     125     31
1     260     15
2     56     16
2     115     49
3     45     71
4     19     11
4     16     48
5     94     52
5     98     47

I want to group records on x , y columns and aggregate on amnt column, in which difference between values of y column be less than 10.

The result is like this:

x     y     sum(AMNT)
------------------------------------------------
1     ?     136
1     260     15
2     56     16
2     115     49
3     45     71
4     ?     59
5     ?     99

What query can I use?

View 6 Replies


ADVERTISEMENT

SQL & PL/SQL :: Group By Count And Date Time Range

Feb 3, 2011

We have a table with timestamp column and having millions of records.We want to create a materialized view or query, which can give count based

-on some group by columns from table and
-group by on condition (if count > 1000) and
-group by on condition (if timestamp range for that group is > 1hr)

View 4 Replies View Related

PL/SQL :: Group By Data Based On Start And End Time In Range

Mar 28, 2013

formatting the data.I want to group the below table data based on the Grade column for a header_data with start_time and end_time displayed in range. I was trying with group by, partitions etc but no luck. I use version 10gr2.

create table rel_data_mf (header_data varchar2(10),start_time varchar2(100),end_time varchar2(100),grade varchar2(1));

--table rel_data_mf data as comma separated values
header_data,start_time,end_time,Grade
ENG,2013-03-29 00:00:00-05:00,2013-03-29 01:00:00-05:00,U
ENG,2013-03-29 01:00:00-05:00,2013-03-29 02:00:00-05:00,U

[Code]...

--Required output

header_data,start_time,end_time,Grade
ENG,2013-03-29 00:00:00-05:00,2013-03-29 03:00:00-05:00,U
ENG,2013-03-29 03:00:00-05:00,2013-03-29 07:00:00-05:00,A
ENG,2013-03-29 07:00:00-05:00,2013-03-29 10:00:00-05:00,U
MATH,2013-03-29 00:00:00-05:00,2013-03-29 03:00:00-05:00,U
MATH,2013-03-29 03:00:00-05:00,2013-03-29 07:00:00-05:00,B
MATH,2013-03-29 07:00:00-05:00,2013-03-29 13:00:00-05:00,U

View 6 Replies View Related

Group Records With Less Than One Hour Separation And Count How Many Per Group

Nov 1, 2013

I'm trying to group sets of data based on time separations between records and then count how many records are in each group.

In the example below, I want to return the count for each group of data, so Group 1=5, Group 2=5 and Group 3=5

SELECT AREA_ID AS "AREA ID",
LOC_ID AS "LOCATION ID",
TEST_DATE AS "DATE",
TEST_TIME AS "TIME"
FROM MON_TEST_MASTER
WHERE AREA_ID =89
AND LOC_ID ='3015'
AND TEST_DATE ='10/19/1994';

[code]....

Group 1 = 8:00:22 to 8:41:22

Group 2 = 11:35:47 to 11:35:47

Group 3 = 15:13:46 to 15:13:46

Keep in mind the times will always change, and sometime go over the one hour mark, but no group will have more then a one hour separation between records.

View 4 Replies View Related

SQL & PL/SQL :: Combining Rownum And Group By Gives - Not A Group By Expression

Jun 23, 2011

I read that rownum is applied after the selection is made and before "order by". So, in order to get the sum of salaries for all employees in all departments with a row number starting from 1, i wrote :

select ROWNUM,department_id,sum(salary) from employees group by department_id

If i remove rownum, it gives the correct output. Why can't rownum be used here ?

View 16 Replies View Related

SQL & PL/SQL :: Group Through Expression Inside Group By?

May 17, 2011

Refer to the txt file to create table and insert data.

I executed the following query-

SELECT priority, detail, COUNT(1) FROM TEST GROUP BY priority, detail

and got the following result-

PRIORITYDETAIL COUNT(1)

StandardPatch 27
StandardInitial TSS 1
StandardInitial development 10
StandardProduction deployment5
High PriorPatch 1

Now I want that Initial TSS and Initial development should be combined as Initial together and I should get the result as follows:

PRIORITYDETAIL COUNT(1)

StandardPatch 27
StandardInitial 11
StandardProduction deployment5
High PriorPatch 1

View 3 Replies View Related

To Add One Disk Group Space To Another Disk Group

May 9, 2011

Is there any way to add one disk group space to another disk group. Because One of my disk group is full i want to add space in to that group.

View 1 Replies View Related

SQL & PL/SQL :: Get Count On Group And Total Count For Each Group

Mar 23, 2013

I'm using this code, and it performs fine, but I'm wondering if there is a more elegant way to do it--maybe with "ROLLBACK". Basically (as you can see) I need to get a normal count for each group but also for each group take a percentage of the total count (so all groups pct adds up to 100 (oh yeah, don't test for zero below, but just a test... )

select
c.Event,
c.code,
count(1) as calls,
total.total_count,
count(1) / total.total_count * 100 as pct_of_total
from
table1 c

[Code]....

[Edit MC: add code tags, do it yourself next time]

View 4 Replies View Related

SQL & PL/SQL :: Count Age In A Range

Mar 26, 2012

I have a table with the following fields :

RGS_CAN_PERIOD NUMBER(4)
RGS_ID_ELECTOR NUMBER(
FBD_NID VARCHAR2(14)
RGS_SURNAME VARCHAR2(40)
RGS_O_NAME VARCHAR2(40)
RGS_ALIAS VARCHAR2(30)
RGS_ADDR VARCHAR2(75)
RGS_ID_REG_AREA VARCHAR2(3)

[Code]..

I need a report as hereunder :

Reg. Area Age <=19 20 <= Age <= 24 25 <=Age <= 29 Total No. of Voters
xxxx 10 15 7 32
yyyy 5 7 3 15

I have work out a script but the age is not in a range

select *
FROM (select rgs_id_reg_area,
count(decode(fbd_age,19,fbd_age)) Age19,
count(decode(fbd_age,20,fbd_age)) Age20
FROM rubyvoterstat where vote ='Y'
GROUP by rgs_id_reg_area)
order by rgs_id_reg_area

View 1 Replies View Related

SQL & PL/SQL :: How To Add Range Partition

Dec 5, 2011

In my prod table few partition need to be added.

I tried below both syntax, but I am getting error

1)
Alter table STS.DNA_ACCESSION add PARTITION DNA_ACC_P143 VALUES LESS THAN (286000000);
ORA-14074: partition bound must collate higher than that of the last partition

2)
ALTER TABLE STS_RO.DNA_ACCESSION
SPLIT PARTITION DNA_ACC_P999 AT VALUES LESS THAN (286000000)
INTO (PARTITION DNA_ACC_P143,

[Code]....

View 4 Replies View Related

SQL & PL/SQL :: Range By Day And Month

Sep 20, 2010

I have table :TABLE_X and want to select some data locate into specific range of Day/Month. But so far i couldn't find out the way to.

For example, i want to select people born within specific range of date(range : sysdate to (sysdate+7months ahead) Year here should not be consider, only the day and month.

e.g. a range could be from today:Sept,20 to Apr,18.

so what i was trying is to select doing the following.

select TABLE_X_ID, TABLE_X_BIRTH_DATE
from TABLE_X
where to_date(TABLE_X_BIRTH_DATE, 'DD/MM')
between to_date (to_char(SYSDATE, 'DD/MM'), 'DD/MM')
and to_date (to_char(SYSDATE+210, 'DD/MM'), 'DD/MM')

first am not sure if BETWEEN & AND will work for this case, bt it was the most logical way i could think about to get such range.

View 13 Replies View Related

How To Find Summary Value By Range

Jul 22, 2011

this is my table

create table sequence ( id int not null primary key);

insert into sequence(id) values
(1), (2), (3), (4), (6), (7), (8), (9),
(10), (15), (16), (17), (18), (19), (20),(22);

i need the answer to group the sequence like this

start_number | end_number | count
1 | 4 | 4
6 | 10 | 5
15 | 20 | 6
22 | 22 | 1

what should i do?

View 1 Replies View Related

Grouping By Time Range

May 19, 2013

I am trying to break down the balance_date to display the following groupings:

7:00-17:30 CDT
18:00-4:30 CDT

I currently have the query setup to display by day instead of these time ranges. I would like the output to read

19 May Day
19 May Night
20 May Day
20 May Night

I am fairly new to this, but how would I go about making this change?

SELECT
TO_CHAR(TRUNC(balance_date,'D') + 4,'YYYY') || '-' ||
TO_CHAR(TRUNC(balance_date,'D') + 4,'IW') as year_wk,
TO_CHAR(TRUNC(balance_date,'D') + 4,'IW')as wk,

[Code] ........

View 1 Replies View Related

Using Range In Oracle Like Statement?

Oct 8, 2009

I am trying to create a SQL query which will check that various postcode formats are valid, but I am having trouble getting oracle sql to check for values within ranges - for example the following returns no rows, even though most of the postcodes I am dealing with start with 'P'.

select postcode from mytable.addresses
where postcode like '[N-R]%'
;

Am I getting my syntax wrong somewhere?

View 3 Replies View Related

SQL & PL/SQL :: How To Find Summary Value By Range

Jul 22, 2011

this is my table

create table sequence (
id int not null primary key
);

insert into sequence(id) values
(1), (2), (3), (4), (6), (7), (8), (9),
(10), (15), (16), (17), (18), (19), (20),(22);

i need the answer to group the sequence like this

start_number | end_number | count
1 | 4 | 4
6 | 10 | 5
15 | 20 | 6
22 | 22 | 1

what should i do?

View 7 Replies View Related

SQL & PL/SQL :: Report With Date Range

Feb 4, 2013

I have a cost data for effective date range as below.

MODEL_NOTIER_COSTEFFECTIVE_START_DATEEFFECTIVE_END_DATE

I3 3770 265 2/3/2013 3/31/2013
I3 3770 269 4/1/2013 5/4/2013

This data needs to be represented in a SQL report as below. The Date Range in the below i.e., Starts with FEB, by checking against the sysdate.. That is from sysdate it will display the Quarter data for 4 months as below.

MODEL NOFEB FY13MAR FY13APR FY13MAY FY13
I3 3770 265 265 269 269

Currently I am using a procedural logic to populate data into a different table in the above format. Is there any method to do with a single SQL using PIVOT. Below given is the table structure and Insert scripts.

CREATE TABLE ITEM_TAG(MODEL_NO VARCHAR2(50), TIER_COST NUMBER(13,4), EFFECTIVE_START_DATE DATE, EFFECTIVE_END_DATE DATE );

Insert statements

Insert into ADMIN_COSTPL_OWNER.ECAPS_ENTITLEMENT
(MODEL_NO, TIER_COST, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE)
Values
('I3 3770', 265, TO_DATE('02/03/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/31/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into ADMIN_COSTPL_OWNER.ECAPS_ENTITLEMENT
(MODEL_NO, TIER_COST, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE)
Values
('I3 3770', 269, TO_DATE('04/01/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('05/04/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;

View 10 Replies View Related

SQL & PL/SQL :: Query On Range Wise

Jul 26, 2013

I want output like given below. Using EMp table . if sal is <1000 means count of Emp's who are having lessthan 1000 like that.

Deptno sal sal sal sal
<=1000 <=2000 <=3000 <= 5000

10 2 4
20 2.

View 2 Replies View Related

SQL & PL/SQL :: Finding Values In A Range?

Nov 1, 2011

@test.sql
accept 1 num prompt 'enter begin value '
accept 2 num prompt 'enter end value '

Need to display all values between the above given inputs.
No tables involved.
Need SQL level solution in version 8i.

View 4 Replies View Related

SQL & PL/SQL :: Range Between X Preceding And Current Row?

Aug 26, 2011

I have a table and want to calculate the stdev of a parameter over a rolling window (past 250 records, sorted by ID):
...
stddev(parameter) over (order by ID range between 250 preceding and current row) as SD
...

I don't get any error, but if I calculate the same in excel (=STDEV(250 row range)), it seems that this code does not the same. Is there something wrong with the 250 row rolling window?

View 4 Replies View Related

Forms :: Range Of Values?

Dec 11, 2010

I have a field called percentage , where user doesnt want to enter any data , instead he wants a poplist like 0-100 when he clik the range will come as 0-10,10-20 and so on like 90-100 how i can do it he will increase and decrease it.

View 1 Replies View Related

SQL & PL/SQL :: Restriction In Date Range?

May 7, 2012

I want my user to be restricted for entering duplicate time within two times.

create table asd(dt_frm date,dt_to date);

insert into asd VALUES(to_date('01-04-2012 08:00','dd-mm-yyyy hh24:mi'),to_date('01-04-2012 10:00','dd-mm-yyyy hh24:mi'));
insert into asd VALUES(to_date('01-04-2012 09:00','dd-mm-yyyy hh24:mi'),to_date('01-04-2012 11:00','dd-mm-yyyy hh24:mi'));

now in the second insertion I want to alert the entry user that 9am already falls in the saved record which is 8am to 10am and so that this record can't be saved.

View 8 Replies View Related

SQL & PL/SQL :: Query Not Using Partition Range?

Dec 8, 2011

I have a table partitioned by Range and subparitioned by hash.

i am issuing the following query .Bcos of security purposes i cannot post the full query.

Select <most of the columns>
FROM qosTesthr PARTITION(PARTITION_ON_2011_11_17)
WHERE starttime BETWEEN to_date('1111170000', 'yymmddHH24mi') AND to_date('1111172359', 'yymmddHH24mi')

Explain plan is

---------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib
---------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 826K| 171M| 2330 (4)| 00:00:28 | | | | | |
|* 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 826K| 171M| 2330 (4)| 00:00:28 | | | Q1,00 | P->S | QC (RAND)

[code]...

When i use INDEX_SS hint in the query then only the partition scan is happening.

---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 826K| 171M| 810K (1)| 02:42:06 | | |
|* 1 | FILTER | | | | | | | |

[code]...

Performance while using the index is very very slow.Is there any way without using the index and just the partition full scan can be forced by using some hint..

View 1 Replies View Related

SQL & PL/SQL :: Create Range Partition?

May 3, 2012

I have to create partition on 1 table where i have date field and id.

i want to create range parttion for both the column.

View 2 Replies View Related

PL/SQL :: Generate Hours Range

Oct 4, 2012

I have a table with a date field. This field storage dates with hours like this:

01-08-2012 8:30:00
01-08-2012 8:15:00
01-08-2012 9:30:00
01-08-2012 9:40:00
02-08-2012 8:30:00
02-08-2012 9:30:00
02-08-2012 9:34:00
...

I have to generate a report with the day and the frequency :

__Day_______ Hour Range__CountRecords
WEDNESDAY 8 - 9 2
WEDNESDAY 9 - 10 2
THURSDAY 8 - 9 3

The block of the hour must be 1 hour (8-9,9-10 and so on)

how generate the hours range.

My database oracle version is 8i. (I know that is very old but I can't change because It´s a legacy system).

View 5 Replies View Related

SQL & PL/SQL :: Generate Records For Range Of Each ID

Sep 13, 2013

write the query for the following requirement.I need to generate the records for the range (Difference between From_val & to_val) ) for each ID

create table test_base(id varchar2(20) , from_val number , to_val number);

insert all
into test_base values ('A', 6,10)
into test_base values ('B', 22,30)
into test_base values ('C', 123,130)
into test_base values ('D', 852,860)
into test_base values ('E', 30 ,30)
select * from dual ;
[code]....

No need to generate any thing for E as the difference is equal to Zero

View 4 Replies View Related

PL/SQL :: Reg - Index On Range In Where Clause?

Oct 29, 2013

I am creating a table where ID is an integer and the where clause will always be in range(for e.g between 200 and 400).

for best performance which index can i create on table such that query runs faster ?(a)clustered(b)non-clustered(c)unique(d)both clustered and non clustered

View 0 Replies View Related

SQL & PL/SQL :: Pick A Value Based On Range

Mar 10, 2012

I need to pick a value based on range like if the range is as below

[code]
if value =2000 then its 2000
elsif value >=2001 and value <=2499 it should be 2000
elsif value =2500 then 2500
elsif value >=2501 and value <=2999 it should be 2500
elsif value = 3000 then it should be 3000
[code]

Like this i need to pick a value by hardcoding this range and this look cumbersome in my program , is there a simple way to substitute this entire thing by passing one single value and getting one single value using a command or builtin.

View 2 Replies View Related

Numeric Precision Specifier Is Out Of Range (1 To 38)

Mar 10, 2004

I am trying to run copy command to copy data from one database to other.I 'm getting this error while running copy command. "ORA-01727: numeric precision specifier is out of range (1 to 38)"

View 1 Replies View Related

How To Take Data When Entering Range Of Dates

Oct 8, 2009

how can i write query to take data from DB, if im entering range of dates :

for example between 20-aug-2009 and 1-aug-2009.

But query must be like, if i enter 21-aug-2009 and second date should the start of the month.

for example if i enter second date like 2-jul-2009, first date should start form begining of that month?

View 1 Replies View Related

Range Partitioning With Number Column

Apr 21, 2011

i have a requirement To partition a Table byRange partition with a Number column. but the issue is the range Must be in a Date datatype ,
For example

partition by range (date_key)
( PARTITION DEF VALUES LESS THAN ('01-SEP-10'))

the date_key column has values of date in number format. like "20101014"

View 2 Replies View Related







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