SQL & PL/SQL :: Ranking Based On A Group?

Jun 29, 2011

I have a table t

CREATE TABLE T
(ID NUMBER(4),
NAME VARCHAR2(40))

INSERT INTO T VALUES (1,'JAMES');
INSERT INTO T VALUES (1,'DOLLY');
INSERT INTO T VALUES (2,'MICHEAL');
INSERT INTO T VALUES (2,'FLASH');
INSERT INTO T VALUES (3,'JAMES');
INSERT INTO T VALUES (3,'MARY');
INSERT INTO T VALUES (4,'JAMES');
INSERT INTO T VALUES (4,'DOLLY');
INSERT INTO T VALUES (5,'JAMES');
INSERT INTO T VALUES (5,'DOLLY');
INSERT INTO T VALUES (6,'JAMES');
INSERT INTO T VALUES (6,'MARY');

SELECT * FROM T ORDER BY 1

ID NAME
1 JAMES
1 DOLLY
2 MICHEAL
2 FLASH
3 JAMES
3 MARY
4 JAMES
4 DOLLY
5 JAMES
5 DOLLY
6 JAMES
6 MARY

each 'ID' has two values always. I want to rank the data based on same pair 'name' in an 'ID'

for example, my desired output is:

ID NAME RANK
1 JAMES 1
1 DOLLY 1
2 MICHEAL 1
2 FLASH 1
3 JAMES 1
3 MARY 1
4 JAMES 2 ---> THAT IS RANK 2 BECAUSE THIS IS THE 2ND TIME JAMES AND DOLLY ARE IN THE SAME 'ID'
4 DOLLY 2 -----> SAME AS ABOVE
5 JAMES 3 ---> THAT IS RANK 2 BECAUSE THIS IS THE 3RD TIME JAMES AND DOLLY ARE IN THE SAME 'ID'
5 DOLLY 3 -----> SAME AS ABOVE
6 JAMES 2 ---> THAT IS RANK 2 BECAUSE THIS IS THE 2ND TIME JAMES AND MARY ARE IN THE SAME 'ID'
6 MARY 2 -----> SAME AS ABOVE

I want the output in exactly above format.

View 3 Replies


ADVERTISEMENT

PL/SQL :: Sum Based On Group 2

Nov 26, 2012

I have the following table and data , I have six employees and carton named A,B,C,D,E and F.

Drop table a ;
Create table a (id number(9), emp_id number(9), cartoon varchar2(20), no_cartton number(9));
Insert into a values(1,1,’A’,10);
Insert into a values(2,1,’B’,20);
Insert into a values(3,1,’D’,25);
Insert into a values(4,1,’E’,15);
[code].......

I have also Third Query: the first and second query already solved in this link . Sum based on group

it is multiple value of carton A of emp_id 1 with 2 + multiple value of B of emp_id 1 and 2 and so on

More detalies : Multiple value of A for Empid 1 and 2 then add it to multiple value of multiple value of b for empid 1 and 2 then add it to multiple value of c of empid 1 and 2

After finished all carton for 1 and 2 then go to 1 and 3 after finished go to 1 and 4 then 2 and 3 then 2 and 4 and so on

Output will be like this
Empid       Total

1 with 2    2050
1 with 3    200
1 with 4    500
1 with 5    2250
1 with 6    700
2 with 3     0
2 with 4    700
2 with 5    4300
2 with 6    1700
3 with 4      0
3 with 5    400
3 with 6     0
4 with 5    400
4 with 6    200
5 with 6    1900

View 2 Replies View Related

PL/SQL :: How To Extract Records Based On Group

Aug 31, 2012

I have a table with with 2 colums serialnumber and brand .

each brand may have multiple serialnumber .

I want to extract 10 serialnumber for each brand .

View 2 Replies View Related

SQL & PL/SQL :: Display Data Based On Group And Percentage?

Feb 12, 2012

I have the following requirement, where I have to display the data based on the group and links when input is given as month. I have written the following code, which is good to display for group. But I want to display for all the groups.
CREATE TABLE target_data
(
T_LINK VARCHAR2(50)
, t_mon varchar2(6)
, t_grp varchar2(30)
, t_views NUMBER

[code]...

When I run this, I get output as

t_grp count_80 goal
grp3 1 0.1

But I want the output as

t_grp count_80 goal
grp3 1 0.1
grp2 1 0.1
grp1 ... ...
grp0 ... ...

View 16 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

PL/SQL :: To Create Function Based Index For Group Function Columns

Jun 15, 2012

Is anyway to create function based index for group function columns.

For example

select max(timestamp),min(age),averge(sal).... ... .. from tab;

View 5 Replies View Related

PL/SQL :: Ranking Sum Total But Not Overall?

Sep 7, 2012

I'm trying to Rank Username based on the Total Sum of amount waived but I want to avoid Ranking the Overall Total at the bottom, plus I dont want them in Ranking order, I want the order to stay the same as it currently is.

SELECT DECODE(GROUPING(USERNAME),1,'TOTAL',0,UPPER(USERNAME)) as "USERNAME",
       SUM(CASE WHEN TO_CHAR(DATE_PROCESSED,'MON') = 'JAN' THEN AMOUNT_WAIVED ELSE 0 END) AS JAN,
       SUM(CASE WHEN TO_CHAR(DATE_PROCESSED,'MON') = 'FEB' THEN AMOUNT_WAIVED ELSE 0 END) AS FEB,
    
[code]....

View 8 Replies View Related

SQL & PL/SQL :: Ranking Non-Consecutive Rows?

Aug 23, 2012

I have a table T with columns

Col1 Col2
1 A
2 B
3 C
4 D

[code]..

I want to do group ranking in desired col3 in such a way that it checks for different values across consecutive rows under col2 and assigns a number to each group. Just when two consecutive rows in col2 have same value then the group ends and the next group starts.

So my desired output is:

Col1 Col2 Col3
1 A 1
2 B 1
3 C 1
4 D 1
5 D 2
6 A 2

[code]...

Here you can see that the first four rows under col2 are unique i.e A,B,C,D so col3 assigns this as group number 1. It ends at row 4 becuase row 5 also has value D under column 2. So in other words, each group must have all unique values and there should not be any repetition. For example, see group 3 (under col3) in above desired output; it starts from row 9 and ends at row 11 because row 12 also has value 'C' and the value 'C' has already occurred in group 3 in row 9.

I want to achieve this SQL. I tried using Dense rank but couldn't go through. I want the shortest possible query to acheive this.

View 3 Replies View Related

Forms :: Ranking Within A Form?

Jul 25, 2013

I have a query find window that allows you to search on various attributes.Also i have a radio button within the query find that allows you to filter the results either as a single record or mulitiple records For example consider the data below.....

Header Table
Record_Id = 1, Record_No = 'Rec1'

Lines Table
Record_Id = 1, Line_desc = 'Line1'
Record_Id = 1, Line_desc = 'Line2'
Record_Id = 1, Line_desc = 'Line3'

I have a View that joins the 2 tables together so for Record_Id = 1 the view returns 3 rows

I would like to have a query find window that allows you to search using:
Record_No
Line_Desc

and has a radio button to allow you to either show the records as a single line or as all detail lines Therefore i would like the following selections:

1) Enter no search criteria but select Single radio option will return 1 record with default line description of Line1
2) Enter no search criteria but select Multiple radio option will return all 3 records
3) Enter Line_Desc = Line1 with Single radio option brings back one record with Line_Desc = Line1
4) Enter Line_Desc = Line2 with Single radio option brings back one record with Line_Desc = Line2
5) Enter Line_Desc = Line3 with Single radio option brings back one record with Line_Desc = Line3
6) Enter Line_Desc = Line1 with Multi radio option brings back one record with Line_Desc = Line1
7) Enter Line_Desc = Line2 with Multi radio option brings back one record with Line_Desc = Line2
7) Enter Line_Desc = Line3 with Multi radio option brings back one record with Line_Desc = Line3

I need the form to select from the view but then perform a rank after it has selected the relavant data. Then the radio button would use the ranking to select either one record or multi records.

View 4 Replies View Related

PL/SQL :: Ranking Partitions - Value Identifier

Jul 4, 2012

Lets say I have a table like the following -

ID--------DATE_TIME-----------------VALUE
101----- 01/01/2012 14:00:00 ---12
101----- 11/01/2012 23:00:00 ---17
101----- 13/01/2012 10:00:00 ---22
101----- 19/03/2012 08:00:00 ---7
101----- 19/03/2012 19:00:00 ---7
101----- 19/03/2012 20:00:00 ---7
101----- 20/03/2012 02:00:00 ---3
101----- 20/03/2012 03:00:00 ---3
101----- 21/03/2012 13:00:00 ---14
101----- 21/03/2012 14:00:00 ---14
101----- 21/03/2012 21:00:00 ---13
101----- 21/03/2012 22:00:00 ---13
101----- 21/03/2012 23:00:00 ---13
101----- 22/03/2012 00:00:00 ---13

I'm looking for a script to partition the data into sections where the VALUE is the same over a constant period of time with no breaks. I'd like to give each partition a value to identify it by.

So the outcome of the script would be the following -

ID--------DATE_TIME-----------------VALUE-----IDENTIFIER
101----- 01/01/2012 14:00:00 ---12----------1
101----- 11/01/2012 23:00:00 ---17----------2
101----- 13/01/2012 10:00:00 ---22----------3
101----- 19/03/2012 08:00:00 ---7------------4
101----- 19/03/2012 19:00:00 ---7------------5
101----- 19/03/2012 20:00:00 ---7------------5
101----- 20/03/2012 02:00:00 ---3------------6
101----- 20/03/2012 03:00:00 ---3------------6
101----- 21/03/2012 13:00:00 ---14----------7
101----- 21/03/2012 14:00:00 ---14----------7
101----- 21/03/2012 21:00:00 ---13----------8
101----- 21/03/2012 22:00:00 ---13----------8
101----- 21/03/2012 23:00:00 ---13----------8
101----- 22/03/2012 00:00:00 ---13----------8

I was trying to do something with trunc(date_time) but that didnt work out right as the blocks of data can carry over several days as seen in the rows with IDENTIFIER = 8.

View 7 Replies View Related

SQL & PL/SQL :: Ranking Function And Duplicate Records

Aug 30, 2012

I have the following table :

CREATE TABLE A_TEST (A INTEGER, B INTEGER, C INTEGER, D INTEGER, FLAG CHAR(11));

INSERT INTO A_TEST (A,B,C,D) VALUES(1,2,3,4);
INSERT INTO A_TEST (A,B,C,D) VALUES(2,4,5,8);
INSERT INTO A_TEST (A,B,C,D) VALUES(1,2,3,4);
INSERT INTO A_TEST (A,B,C,D) VALUES(2,4,5,8);
INSERT INTO A_TEST (A,B,C,D) VALUES(7,2,3,4);
INSERT INTO A_TEST (A,B,C,D) VALUES(9,2,3,4);
[code]....

I would like to perform an update on the FLAG column by setting to "D" if it is a duplicate record.1,2,3,4);I would like to use the rank function.

Desired update:

A B C D FLAG
1 2 3 4
2 4 5 8
1 2 3 4 D
2 4 5 8 D
7 2 3 4
9 2 3 4
7 2 3 4 D
1 2 3 4 D
5 4 5 8
2 2 3 9
2 4 5 8
6 2 3 4
1 3 3 4
8 2 8 4

View 3 Replies View Related

SQL & PL/SQL :: Analytic Functions And Ranking / Ordering?

Jul 17, 2008

I need to return an ordered list of documents. The documents may belong to a set id (optional) and if so, are either a "master" or a "duplicate" type. For each set there can be only one master but many duplicates. My goal is to group all the sets together such that each master is proceeded by its duplicates.

Table description:
document_master_duplicates
(
documentid,
duplicate_setid,
is_master
)

This needs to join to another table briefcase_documents which contains our set of documents. The briefcase / document relationship is many-to-many.

Table description:
briefcase_documents
(
briefcaseid,
documentid
)

There's also a documents table containing the documentid and among other things a page_count. In the following example I want to sort the documents first by page count but preserving the master/dupe grouping. Any documents which don't belong to a set or are just a duplicate without a master i want at the end of my set but also ordered by page count.

Here's an example set that I would want to order by:

DocumentId Page_Count SetId Is_Master
2002 2 1 0
2003 20 2 0
2008 20 NULL NULL
2010 20 4 0
2012 1 4 1
2001 5 1 1
2004 16 3 1
2011 17 4 0
2014 10 5 0
2009 9 NULL NULL

As you can see I have a little bit of everything here. Docs 2001 and 2002 are the typical set of 1 master and its duplicate. Docs 2010, 2011, and 2012 is the same just a set of 3. Doc 2004 is a master but without any duplicates. Docs 2003 and 2014 are duplicates without a master (these docs have a master in the table but that doc isn't in the set i need to order by). Docs 2008 and 2009 do not belong to a set and as such do not have a master/dupe type.

The result i'm looking to achieve will be ordered as follows:

DocumentId Page_Count SetId Is_Master
2012 1 4 1
2011 17 4 0
2010 20 4 0
2001 5 1 1
2002 2 1 0
2004 16 3 1
2009 9 NULL NULL
2014 10 5 0
2003 20 2 0
2008 20 NULL NULL

As I said above I first want to get the groupings of master/dupes and order ascending on the masters page count. For each duplicate of a master I then want to order the duplicates by page count. After I finished ordering all the master/dupe groups I then want to move on to the rest of the documents which will contain documents that don't belong to a set along with documents which are duplicates but have no master in my set. However, documents which are masters but without duplicates should have been ordered along with the other master/dupes groupings.

With this all in mind I have just been completely overwhelmed as to where to even start. Am I using analytic functions? Hierarchical stuff?

View 10 Replies View Related

SQL & PL/SQL :: Ranking Of Consecutive Non-increasing Values In A Column?

Apr 14, 2012

I have a table with numbers like this in a column

sr no. value
1 17
2 0
3 0
4 38
5 32
6 24
7 12
8 51
9 42
10 1
11 1
12 0
13 0
14 1
15 1
.
.
.
so on....

I want to find out consecutive non-increasing sequences of value (2nd column) order by sr. no (first column) in ascending order.

For example, in the 2nd column, 17 is followed by 0 and 0 and then 38 so it means 3 consecutive values (i.e starting from 17 are 0 and 0) are non-increasing and they are ranked by '1' in my desired in third column as shown below. similarly, the 2nd non-increasing sequence starts with 38,32,24 and 12 and this is ranked as '2' in the third column. same is the case with rank '3' for the third non increasing sequence. so bascially i want the third column with "ranks" starting and ending as per above logic. i tried using LEAD function but doesn't get what I want. I need the shortest possible query to get that third column since i have other columns in the original table in a multiple group by query.

my desired output is:

sr no. value Rank
1 17 1
2 0 1
3 0 1
4 38 2
5 32 2
6 24 2
7 12 2
8 51 3
9 42 3
10 1 3
11 1 3
12 0 3
13 0 3
14 1 4
15 1 4
.
.
.

View 3 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 :: GROUP BY

Oct 10, 2011

CREATE TABLE TEMP_GP_SEQ
(
COL1 NUMBER,
COL2 NUMBER,
COL3 NUMBER,
COL4 NUMBER,
COL5 NUMBER,
COL6 NUMBER,
COL7 CHAR (25)
);

[Code]...

I need to update the TEMP_GP_SEQ table and on the COLUMN COL7 based on the grouping range mentioned above using the sequence created. But the sequence should not be incremented for each and every record, it should be incremented only for change of groups. For example

SELECT *
FROM TEMP_GP_SEQ;

COL1 COL2 COL3 COL4 COL5 COL6 COL7
110100
110101
12101001

[Code]...

After update it should look like

SELECT *
FROM TEMP_GP_SEQ;

COL1 COL2 COL3 COL4 COL5 COL6 COL7
110100
110101
12101001M2

[Code]...

Actually the group by fetched two rows with a total record count of 4 (2 records in both the rows). SO the COL7 is updated for these four rows with M as a prefixed word and the number followed after M is from the sequence. Here the number is changing (sequence is incrementing) only when there is a change in grouping criteria and it remains the same throughout a group.

View 19 Replies View Related

Using GROUP BY To Get Counts

Jan 22, 2008

how to logically connect two tables. Here is an example of what I'm trying to do:

HOSTS TABLE CPU TABLE
----------- ---------
ID HOST ID CPU
01 host1 01 proc01
02 host2 01 proc02
03 host3 02 proc01
02 proc02
02 proc03
02 proc04
03 proc01

Based on the above, I can see that 'host1' has 2 CPUs, 'host2' has 4 CPUs, and 'host3' has 1 CPU. What I'd like to do is create a query that would output:

HOST CPU
----------
host1 2
host2 4
host3 1

I'm looping through the hosts and passing the them into another query as bind variables. That's slow and cannot be the best way to do this. I'm aware that I need to using a GROUP BY HAVING COUNT, but that doesn't seem to be working. It tends to return the total count of all CPUs rather than CPUs per host.

View 2 Replies View Related

Group Policy In VPD

Jun 19, 2013

Give me some examples of creating grouped in VPD .

Group policy example for
1) Grouped policy on same table for different columns
2) Grouped policy on two different table for different columns

View 1 Replies View Related

SQL & PL/SQL :: Index And Group By

Sep 5, 2012

I have a query that extracts the row with a max(record_date) within a group.

select ssn, fund, type_indicator, annuitant, cur_year, record_date, guarantee
from LC_MORTALITY
where (ssn, fund, type_indicator, annuitant, cur_year, record_date) in
(select ssn, fund, type_indicator, annuitant, cur_year, max(record_date)
from LC_MORTALITY
group by ssn, fund, type_indicator, annuitant, cur_year);

the table has index that matches the group by clause exactly.

create index IDX_LC_MORTALITY_sftayd on LC_MORTALITY
(SSN,
FUND,
TYPE_INDICATOR,
ANNUITANT,
cur_year,
record_date

However, the plan ignores the index

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1963K| 241M| | 1375K (4)| 05:21:04 |
|* 1 | HASH JOIN RIGHT SEMI| | 1963K| 241M| 9701M| 1375K (4)| 05:21:04 |
| 2 | VIEW | VW_NSO_1 | 145M| 8038M| | 241K (12)| 00:56:22 |
| 3 | HASH GROUP BY | | 145M| 8038M| | 241K (12)| 00:56:22 |
| 4 | TABLE ACCESS FULL| LC_MORTALITY | 145M| 8038M| | 219K (3)| 00:51:13 |
| 5 | TABLE ACCESS FULL | LC_MORTALITY | 145M| 9840M| | 222K (5)| 00:51:49 |
---------------------------------------------------------------------------------------------
[code]...

It uses the index but still shows "table access full" in one place. Why is there still a full access?Can I do anything to optimize further?

View 9 Replies View Related

SQL & PL/SQL :: How To Group By Data

Sep 8, 2011

My requirement is Data from a TableA has to be provided as an overall view

TABLEA
ID ENTITY REQ_FLG PAR_FLG EXT_FLG
CONV_1 ACCNT Y Y Y
CONV_1 PROD Y Y N
CONV_1 ADDR Y N N
CONV_2 DID Y N N
CONV_2 ORDER Y N N

Required to show the data in report as

ID Expand View_Report Populate ENTITY QRY_STATUS
CONV_1 Expand Report Populate
ACCNT Y Y Y
PROD Y Y Y
ADDR Y N N
CONV_2 Expand Report Populate
DID Y N N
ORDER Y N N

Where "Expand", "Report", "Populate" are provided as Hard coded values in query.

Sample Query.
SELECT ID
,'Expand' AS EXPAND
,'Report' AS VIEW_REPORT
, 'Populate / Reset' AS POP
, DECODE(MN_TBL.ENTITY,NULL,NULL,ENTITY) AS ENTITY
, REQ_FLG || ' ' || PAR_FLG || ' ' || EXT_FLG AS QRY_STATUS
FROM TABLEA
GROUP BY GROUPING SETS
((ID), (ENTITY, REQ_FLG , PAR_FLG , EXT_FLG ))
ORDER BY CONVERSION_ID, ENTITY

Above query works fine, where single ID is present

ID Expand View_Report Populate ENTITY QRY_STATUS
CONV_1 Expand Report Populate
ACCNT Y Y Y
PROD Y Y Y
ADDR Y N N

But when more than one ID is present the entire thing collapses

View 5 Replies View Related

SQL & PL/SQL :: Group By Thursday

Jun 26, 2013

My weekly reports should start from Thursday and it ends on Wednesday . I need to aggregate data from Thursday to Wednesday. At the end of every month i generate report for the last 4 weeks.

I couldn't find a way to group by correctly. When I make group by trunc(fragment_date,'day')+2 and check the data it aggregates data from Monday to Sunday.

View 7 Replies View Related

SQL & PL/SQL :: Group By Statement

Oct 27, 2011

SELECT COUNT(c.country_id)
FROM countries c,
employees e,
locations l,
departments d

[Code]....

I really dont know when and where to use the group by statement from the query above when I added C.country name before the word from I'm always getting this error "ORA-00979: not a GROUP BY expression"

View 12 Replies View Related

SQL & PL/SQL :: Group By Clause

Mar 31, 2011

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

View 5 Replies View Related

SQL & PL/SQL :: Selecting 1 Row From A Group?

Jul 3, 2011

I am having trouble selecting the row that has max(pay_period) of 13. Here is some test data.

CREATE TABLE TESTME
(
SSN VARCHAR2(11 BYTE),
PAY_PERIOD VARCHAR2(3 BYTE),
PAY_YEAR NUMBER,
KRONOS_ID VARCHAR2(6 BYTE),
LAST_NAME VARCHAR2(15 BYTE),
FIRST_NAME VARCHAR2(14 BYTE),
ADJ_SALARY NUMBER
)

[code]....

View 17 Replies View Related

SQL & PL/SQL :: Group By For Getting Count

Mar 7, 2013

I need suing group by for getting count.I have a table with columns below

SYS_AUDIT_IDSYS_AUDIT_PROG_IDPROG_FINDING_ID_COUNT

178921652
178921641
178921631
179321521
179321511
179321501
179321491
179321461

I want to count number of SYS_AUDIT_PROG_ID for each audit and count of PROG_FINDING_ID_COUNT

I want to get
1789 3 4

I tried this query but this is not working

[code]select sys_audit_id ,count (sys_audit_prog_id), count(prog_finding_id_count) from

my_table sub
group by sys_audit_id [/code]

View 2 Replies View Related

SQL & PL/SQL :: Other Columns When Using Group By

Feb 18, 2011

I have a table OS_CURRENTSTEP , and OS_WFENTRY

CREATE TABLE OS_CURRENTSTEP
(
ID NUMBER,
ENTRY_ID NUMBER NOT NULL,
STEP_ID INTEGER NOT NULL,
ACTION_ID INTEGER,
OWNER VARCHAR2(20 BYTE),
START_DATE DATE,

[Code]...

I need count of step_id from os_currentstep wh

here is the query

select count(step_id), step_id from os_currentstep where owner='Marty' group by step_id

this gives me the count I also need the name associated with this step_id from table OS_WFENTRY

, I cannot query name from step_id ,

View 5 Replies View Related

SQL & PL/SQL :: Group Functions

Jul 2, 2010

count the no: of emp working under each manager? and instead of manager number display the manager name

View 5 Replies View Related







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