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
ADVERTISEMENT
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
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
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
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
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
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
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
View Related
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
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
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
Jul 8, 2009
I am writing a report that breaks on the first 4 fields. That part is working fine. I also want a count for each Group (the 4 fields), and a grand total. Since I want to break on all 4 fields as if they are one combined field, I made a concatenated column (called Break_key) and had the report total on that.
I was surprised when the count appeared at the top of each group, rather than at the bottom. The grand total is at the very bottom of the report, as I would have thought. How can I get the sub-totals at the bottom, rather than the top?
Report Builder 9.0.4.0.33
My query:
select distinct
fs.FUNDING_SOURCE ,
lpad(l.circuit, 2, '0') circuit,
l.DEPARTMENT ,
l.REIMB_TYPE ,
p.POSITION_NO ,
decode (p.position_type, 'F', 'Full', 'S', 'Shared') position_type ,
ph.phasein,
[Code]....
View 2 Replies
View Related
Aug 27, 2010
I would like to give back to the our application user a page of results for a given query along with the total result count, something like: "Showing 1-25 of 650 total results".
Currently I am doing this by submitting a second query:
select count(*) from (<previous query criteria>)
Is there a better performing approach I could be using?
View 4 Replies
View Related
May 31, 2013
I need to modify my query so that it can give me a total(duration) and total(stlmntcharge) per day in april 2013 starting from the 1st till the 30th. At the moment my query looks like below:
SELECT sum(duration),sum(stlmntcharge)
FROM voipcdr
WHERE (calldate >= TO_DATE('20130401','YYYYMMDD') AND calldate <= TO_DATE('20130430','YYMMDD'))
AND (remtrunkid IN (SELECT UNIQUE trunkid FROM trunks WHERE description LIKE '%Telkom%' AND gw_range_id = '61' AND trunkid like '9%'))
or remip in(SELECT UNIQUE startip FROM gateways WHERE rangename LIKE 'vo-za%' OR rangename LIKE 'PC-IS-VOIS%')
AND direction = 'I'
ORDER BY calldate, calltime;
View 6 Replies
View Related
Apr 29, 2010
i need the total of the count query I am executing.My query is
SELECT COUNT(*) FROM po_headers WHERE
CREATION_DATE BETWEEN '01-MAR-2010' and '31-MAR-2010' GROUP BY VENDOR_ID
this gives output as
6
1
3
4
My objective is to find the total of distinct vendors for a given date range.how should I modify this query.
View 1 Replies
View Related
Feb 24, 2009
I have a table A which has million records, and one of the column is a date. All records prior to 01-FEB-2009 should be deleted.
I cannot run the below code considering the amount of records or rows in the table A.
Begin
Delete from A where trunc(date_column) < '01-FEB-2009';
commit;
End;
where we can delete the rows prior to 01-FEB-2009 and also have a total count of how many were deleted?
View 14 Replies
View Related
Jan 14, 2013
We have a front end that is polling the database for some set of data.That set of data is returned by opening a ref cursor and passing it back to the calling environment.Now the problem they also want the count of total number of records that will be fetched by my select statement.One option is execute the select statement once ,get the count and pass it.But in that case i will be executuing the query twice once for count other time while openimng for the ref cursor .
View 7 Replies
View Related
Dec 5, 2012
i have a table below; sql> desc css_survey
SURVEY_ID NOT NULL NUMBER(5)
USER_ID NOT NULL VARCHAR2(15)
ACADEMIC_SEMESTER VARCHAR2(25)
Q1 NUMBER(1)
Q2 NUMBER(1)
below are the records
survey_id user_id academic_semester q1 q2
1 1 2012 2 3
2 2 2012 2 3
3 3 2012 3 4
4 4 2012 3 4
5 6 2012 2 4
the Q1 and Q2 could have values 1,2,3,4,5 thats means total are 5 questions. i need to know the total users who select value from q1 group by the values from 1..5 the toal_users who select value from q2 values( a group).i need the following result
total_users question_Q1_select question_Q2_select
3 2
2 3
3 4
2 3
View 2 Replies
View Related
Jun 13, 2011
I want to know that whether we can write a query to add total number of count of each table.
for example:
select count(*) from emp;
count(*)
14
select count(*) from emp_1;
count(*)
15
select count(*) from emp_2;
count(*)
16
My question is that is it possible to add this all the three results and get the value as 45.
View 3 Replies
View Related
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
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
Nov 16, 2009
When we execute select count(*) from table_name it returns the number of rows.
What does count(1) do? What does 1 signifies over here? Is this same as count(*) as it gives the same result on execution?
View 13 Replies
View Related
Nov 24, 2011
difference between count(1) and count(*). As i know count(*) will give number of rows irrespective of null and count(1) will not count the null.My Oracle version is 10 g.
SQL> select * from t1;
A B C
---------- -------------------- --------------------
1 2 3
2
5
SQL> select rownum,a.* from t1 a;
ROWNUM A B C
---------- ---------- -------------------- --------------------
1 1 2 3
2 2
3 5
4
[code]....
View 3 Replies
View Related
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
May 24, 2010
I have the folloiwng two queries:
Query_1: select count(*) yy from table1;
Query_2: select count(*) zz from table2;
I need to compute the following:
var:=(yy/zz)*100
How can I achieve this in a single query?
View 3 Replies
View Related
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
Feb 28, 2013
I have one tbale like
TABLE A with columns bu_code and bu_cty
bu_code bu_cty
12 ES
12 ES
13 AP
13 AP
so now i want to know the count for bu_code
like my output shd be
bu_code(12) ---count is 2
bu_code(13)------count is 13
View 4 Replies
View Related
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
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
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
