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.
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]
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;
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
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:
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.
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;
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)
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)
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.
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 ?
I have one table employee where there are 4 fields ,emp_code,emp_locn,emp_job_code,emp_job_desc the problem is i am trying to prepare a group reports based on location and emp_job_code there is a duplication of data in the emp_job_desc ,
For example there is a job_code E2 Which has two different job_descriptions for two different employees like E2-PAINTER-SPRAY, E2- PAINTER -SPRAY, another example is E1-rigger , E2-RIGGER and so on.Is there a method to match them together as one description.
I have table which contains huge data. around 12 lakhs records. when I use sum function on accountname and docdate it gives wrong value. once I restart the server it gives the correct value. one or two days it gives correct value after that again I get the same problem. If I restart again it gives correct value.
I use Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 64 bit server on Linux.
If I run the following query I got 997 records by using GROUP BY.
SELECT c.ins_no, b.pd_date,a.project_id, a.tech_no FROM mis.tranche_balance a, FMSRPT.fund_reporting_period b, ods.proj_info_lookup c, ods.institution d WHERE a.su_date = b.pd_date AND a.project_id = c.project_id AND c.ins_no = d.ins_no AND d.sif_code LIKE 'P%' AND d.sif_code <> 'P-DA' AND a.date_stamp >='01-JAN-2011' AND pd_date='31-MAR-2011' GROUP BY c.ins_no, b.pd_date, a.project_id, a.tech_no;
I want to show the extra columns a.date_stamp and a.su_date in the out put so that I have used PARTITION BY in the second query but I got 1079 records.
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
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.
I have written the following PL/SQL procedure to delete the records and count the number of records has been deleted.
CREATE OR REPLACE PROCEDURE Del_emp IS del_records NUMBER:=0; BEGIN DELETE FROM candidate c WHERE empid in (select c.empid from employee e, candidate c where e.empid = c.empid and e.emp_stat = 'TERMINATED' ); [code]....
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.
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
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.
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"
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),
