Group By Multiple Columns / Count And Then Find A Max

May 24, 2008

I have three tables,let's say

table stores
sid | store_name
1 | one
2 | two
3 | three

table products
pid | sid | p_name
1 | 2 | pone
2 | 2 | ptwo
3 | 3 | pthree

table sales
said | sid | pid
1 | 2 | 1
2 | 3 | 1
3 | 2 | 2
4 | 1 | 3
5 | 2 | 2
6 | 3 | 2
7 | 3 | 2

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)

View 5 Replies


ADVERTISEMENT

PL/SQL :: Multiple Columns Using Group By

May 20, 2013

Can i select multiple columns while using group by single column?

View 5 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 :: 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 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 :: How To Increment Count When GROUP By Is Used

Feb 17, 2010

SNO column should be incremented depending upon the total records to be fetched.if i get 4 records then sno numbers should be 1,2,3,4.i dont want to put rownum also in the GROUP BY clause.how to increment the serial number?

SELECT JC.A,
ROWNUM SNO, --serial number
JC.B,
SUM(CHR.AMOUNT),
SUM(CHR.FINALAMOUNT),
JC.C,
JC.D,
C.E,
JC.F,
JC.G
FROM CHARGES CHR
WHERE JC.B = '12111'
AND JC.STATUS = 'INVOICED'
GROUP BY JC.A, JC.B, JC.C, JC.D, JC.E, JC.F, JC.G;

View 8 Replies View Related

Count And Group Query?

Apr 23, 2012

I have a report I created and I need to get a Total Count by Plan Code Description for Each State. I do this and get all my data:

SELECT
mv.R1_State,
mv.subscriber_id,
mv.plan_code,
pm.description,
mv.line_of_business,

[code]...

But I cannot get the count I have to do a separate Query to get the count here it is How can I put the two together to get my count information and Report information together in one Report???

Select
Count(pm.description),
mv.R1_State
FROM windsoradm.member_mv mv

[code]....

View 1 Replies View Related

PL/SQL :: Count / Group By Between Dates

Oct 30, 2013

I am trying to count the number of IDs dropped and enrolled in each unit for each of the 4 terms between their perspective dates. There are 4 Terms and an ID can participate in a unit in any of these terms:

TERMSTART_DATEEND_DATE125-Feb-1318-Mar-13227-May-1317-Jun-13326-Aug-1316-Sep-13425-Nov-1316-Dec-13 .

I am trying to count how many IDs enrolled in a unit between those dates and how many doped before the end dateThe ENROL_DATE for each ID in a unit has to be between the given Term Dates to count. Unit KIS has 1 ENROL and one DROP in TERM 1 UNIT POL occurs in TERM 2 and 4 and both DROPUNIT LIN and 1 ENROL and 1 DROP in 2 different TERMS My problem is how do i specify count ENROL and count drop between the Term dates and then group by TERM and UNIT. 

{code}CREATE TABLE DAN_GR4(ID             NUMBER(12),STATUS           VARCHAR2(12),TERM             NUMBER(12),ENROL_DTE       DATE,TERM_START_DTE  DATE,TERM_END_DATE   DATE,UNIT            VARCHAR2 (12));  INSERT INTO  DAN_GR4 (ID,STATUS,TERM,ENROL_DTE,TERM_START_DTE,TERM_END_DATE,UNIT) VALUES ('1',    'ENROL'    ,'1',    '15-Mar-13'    ,'25-Feb-13' ,'18-Mar-13',  'KIS');INSERT INTO  DAN_GR4 (ID,STATUS,TERM,ENROL_DTE,TERM_START_DTE,TERM_END_DATE,UNIT) VALUES ('1',    'DROP'    ,'2',    '27-MAY-13'    ,'27-MAY-13' ,'17-JUN-13',    'POL');INSERT INTO  DAN_GR4 (ID,STATUS,TERM,ENROL_DTE,TERM_START_DTE,TERM_END_DATE,UNIT) VALUES ('1',    'DROP'    ,'2',    '27-JUN-13'    ,'27-
[code].....

View 2 Replies View Related

Count / Group Not Working?

Dec 1, 2007

I am using oracle, and have the following relations.

Patient(Patno, name,docno*)
Doctor(Docno, name, specialization)
Treatment(treatno, startdate, reason, docno*, patno*)

I want to list every patients number and name, and the number of treatments given by a certain doctor (e.g doctor name = 'smith'). the problem i am having is that if i specify the doctor is WHERE clause it only gives me the doctors who are under 'smith' but i want all even if 'smith' has given them 0 treatments. below is code that i have so far to view all patients with corresponding data.

select pa.patno, pa.lname, tr.treatno
from patient pa full join treatment tr on tr.patno=pa.patno;

View 1 Replies View Related

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

Performance Tuning :: Against Count With Group By Clause

Mar 31, 2012

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.

View 4 Replies View Related

PL/SQL :: Merge Multiple Rows Into Single Row (but Multiple Columns)

Oct 17, 2012

How to merge multiple rows into single row (but multiple columns) efficiently.

For example

IDVal IDDesc IdNum Id_Information_Type Attribute_1 Attribute_2 Attribute_3 Attribute_4 Attribute_5
23 asdc 1 Location USA NM ABQ Four Seasons 87106
23 asdc 1 Stats 2300 91.7 8.2 85432
23 asdc 1 Audit 1996 June 17 1200
65 affc 2 Location USA TX AUS Hilton 92305
65 affc 2 Stats 5510 42.7 46 9999
65 affc 2 Audit 1996 July 172 1100

where different attributes mean different thing for each Information_type. For example for Information_Type=Location

Attribute_1 means Country
Attribute_2 means State and so on.

For example for Information_Type=Stats

Attribute_1 means Population
Attribute_2 means American Ethnicity percentage and so on.

I want to create a view that shows like below:

IDVal IDDesc IDNum Country State City Hotel ZipCode Population American% Other% Area Audit Year AuditMonth Audit Type AuditTime
23 asdc 1 USA NM ABQ FourSeasons 87106 2300 91.7 46 85432 1996 June 17 1200
65 affc 2 USA TX AUS Hilton 92305 5510 42.7 46 9999 1996 July 172 1100

View 1 Replies View Related

PL/SQL :: Count Number Of Employees In Every Department - Group By Clause Not Working

Oct 31, 2012

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.

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 :: Count In Columns?

Feb 14, 2012

I have the following table with insert below.

CREATE TABLE ATTENDANCE
(
"REG_NO" NUMBER(7,0),
"COURSE_SEQ_NO" NUMBER(4,0),
"SECTION" VARCHAR2(1 BYTE),
"SEMESTER_CODE" NUMBER(1,0),

[code]...

How can I have the following output?

course_seq_nosectiontotal_recordscount_1count_0
322X20137

I tried decode but it could not find its way

View 9 Replies View Related

PL/SQL :: Count Columns In A Row

May 3, 2013

oracle SQL,

Student(unique) has five races in a table

I need a select statement with student_id - 1 column

I need to count the number columns with values 'Yes' in them

the columns are race1,race2,race3,race4,race5 for a single row --- this should be 2 ndcolumn

The 3rd column should contain the max of the race having 'yes' in them

i.e if race1 and race 3 have values'yes' in them i need 3 in the next column, - 3rd column

View 5 Replies View Related

SQL & PL/SQL :: Multiple Rows On A Table To Multiple Columns On One Row

Nov 26, 2010

I am attempting to select back multiple values for a specific key on one row. See the example below. I have been able to use the sys_connect_by_path to combine the fields into one field but I am unable to assign them to fields of their own. See the example below

TABLE DETAILS:
Policy id plan name
111 A Plan
111 B Plan
111 Z Plan
112 A Plan
112 Z Plan

My desired result is to be able to show the output as follows

Policy ID Plan_1 Plan_2 Plan_3
111 A Plan B Plan Z PLan
112 A Plan Z PLan

View 6 Replies View Related

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;

This query gives following result

3525125240000USDINR
35251250 USDINR
352512560000 USDINR
352512550000 USDINR

There are two distinct currency codes (USD, INR) and total amount is 350000. So I am looking for a query to give me the following result

3525125350000 2

View 9 Replies View Related

PL/SQL :: Adding Columns To Count Contents Of Other?

Aug 31, 2013

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,

[code]....

View 2 Replies View Related

Find Out Row Count From Each Table?

May 6, 2009

I have written a script to get count of each tables of a schema.

DECLARE CURSOR c1 IS
select distinct table_name from all_tab_columns where OWNER='PLAY';
countrows NUMBER;

[Code].....

View 1 Replies View Related

Find Out Row Count From Each Table

Mar 31, 2009

SQL> select count(*) from tab;
COUNT(*)
----------
63

I need to find out how many rows in each table.I've tried with cursor.

View 9 Replies View Related

SQL & PL/SQL :: How To Find The Count In The Query

Apr 16, 2010

from the below string i want to find the count of the "<-" using a single query.

string is "aaaa<-bbbb<-ccccc<-ddddd<-eeeeee<-ffffff<-"

If not the query, pointer on using SQL functions.

View 3 Replies View Related

PL/SQL :: Count To Find Percentage

Sep 26, 2012

I want to find the Pass rate for each term by adding up the grades.

PASS = HD/D/PASS FAIL = FAIL and Z means no mark has been recieved yet so i do not want to include it in the calculation.

CREATE TABLE DAN_GR
(ID     VARCHAR2(8),
TERM    VARCHAR2(8),
GRADE VARCHAR2(8),
SUBJECT          VARCHAR2 (8),
STAT        VARCHAR2 (8))

[Code]....

So term 1201 has 1 Pass 1 Fail and a Z (which is not counted) so pass rate is 50%
term 1202 has has 1 Pass only so the pass rate is 100%
term 1203 has 1 Fail so pass rate is 0%
term 1204 has 1 Pass and 2 Fails and i dont count the 3rd fail because the stat is 'APSENT' i only want to count the once where stat is 'PRESENT'

WANT:

COUNT     TERM     PASS RATE
3     1201     50%
1     1202     100%
1     1203     0%
3     1204     33%

I am using:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi

View 4 Replies View Related

SQL & PL/SQL :: Find Maximum Value Within A Group For Corresponding Types

Oct 25, 2010

Test data for the problem is as follows.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> with t
2 as
3 (
4 select 1 id, 12 compid, 1 rel_type, null enddt from dual union all
5 select 1, 13, 1, to_date('31.12.1993','dd.mm.yyyy') from dual union all
6 select 1, 14, 1, to_date('12.06.1996','dd.mm.yyyy') from dual union all
7 select 1, 15, 1, to_date('23.04.2003','dd.mm.yyyy') from dual union all
[code].......

I want to find the the latest compid for individual types 1 and 2 only within a list of ids. Latest compid is defined as the compid associated with the latest end date (null is treated as the latest end date).

So in the above example for rel_type = 1, compid : 12 is latest, for rel_type : 2, compid : 6 is latest.

Datatype for the tables are :

Id : Number
compid : Number
Rel_Type : Number
enddt : Date

View 4 Replies View Related

SQL & PL/SQL :: Find Count Of Customers Which Are In Same Campaign

Apr 16, 2013

The communication table has around 1 billion rows which is partitioned by a date column which is not used in the query. The hierarchy table has only 10000 rows. rewrite the query if its possible.Its trying to find the count of customers[distinct] which are in the same campaign.

select h.brand_cd, h.campaign_name, h.campaign_start_dt, h.campaign_name,h.campaign_desc,h.subject, count(distinct cc.customer_id)
from communication cc,hierarchy h
where cc.campaign_hierarchy_id = h.campaign_hierarchy_id
and h.brand_cd in ('ABY','AYO')
AND H.CAMPAIGN_OWNER = 'WONDER'
and h.campaign_start_dt >'1-JUL-2011'
group by h.brand_cd, h.campaign_name, h.campaign_start_dt, h.campaign_desc,h.subject;

View 1 Replies View Related

SQL & PL/SQL :: Select Columns Of 3 Tables In Such A Way That Period Column Should Be In Group By Function

Aug 16, 2011

i 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

View 13 Replies View Related

SQL & PL/SQL :: Query To Find Count Of Delimiter In Column Val?

Jun 29, 2010

Need a query to find the count of presence of delimiter "," in column VAL for below mentioned table.

Table:

CREATE TABLE N_B_S_1
(
NUM NUMBER,
VAL VARCHAR2(20)
)

Data:

INSERT INTO N_B_S_1 VALUES(1,'A,B,C');
INSERT INTO N_B_S_1 VALUES(2,'D');
INSERT INTO N_B_S_1 VALUES(3,'ER,NF,G,H,XK');
INSERT INTO N_B_S_1 VALUES(4,'LQW,MBV');
INSERT INTO N_B_S_1 VALUES(5,'KS,YJ,WE,RQ,PM');
COMMIT;

Required output:

num, count of delimiters
===== ===========
1 2
2 1
3 4
4 2
5 4

View 5 Replies View Related

How To Create SP For Taking Count Of Multiple Tables

Dec 7, 2012

i have 20 tables in schema and wanted to take count using store procedure.

how to create a sp for taking count of multiple tables

View 5 Replies View Related

Server Administration :: What Are ASMlib Devices And How To Find Group Permission Of It

Jan 18, 2013

what are ASMlib devices and how to find the group permission of it ?

View 1 Replies View Related

Real Application Clusters :: Multiple Disk Group Pros / Cons

Jul 8, 2013

i am trying to identify the pro's/con's of using multiple ASM Diskgroup.  I understand oracle recommends/best practice is to have 2 DG (one data and one flash) and you can place multiple copies of control files/online redo logs(and thats the way i want to go).  But would that same be true if i use different set of DISK.  For example we have multiple RAID 10 devices and multiple of SSD devices for us that we can use for this ASM instance.  And i was thinking to create 2 more Disk group (call it DG_SYS1 and DG_SYS2)  and use that to put my online redo logs, control file and temp and system table space there.   i understand in a standalone system(where regular file system is being used), they(online redo/ control file) are usually on there own drives, but with ASM when i am already using external RAID 10 config + ASM striping i assume the IO would faster or am i better of using the SSD that i can have for my redo/control?  What would be the pro's/cons of it (besides managing multiple DG)..

View 7 Replies View Related







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