i am facing group by issue when running the query.the error code is ORA-00979: not a GROUP BY expression.i suspect is due to the subquery in the SELECT clause.
SELECT WHINR100.COMPANY, WHINH210.SEQN, FXINH039.OTBP, WHINR100.BPID, TCCOM100.NAMA, WHINR100.ITEM, WHINH210.RCNO, FXINH051.btch, case when (whinh210.ORNO in (select fxinh033.pdno from fxinh033)) [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
MEASURE_VALUE(ID, VALUE) containing measure values. I would like to calculate the average of a specific id interval. In my case the id is the position where the value was captures.
I am calling one record group to get the security permissions. That RC is associated with one SELECT query. That RC is already created and used before i call. I m just re-using the same. But my form/module dont have privilages to see the QUERY in that recrod group. I just want to see the query in side that record group. how to print the SELECT query from the record group.
SELECT CASE WHEN currentstep.step_id IN (100) THEN currentstep.start_date ELSE (SELECT start_date FROM audt.os_historystep WHERE ID =
[code].....
here is the completed query
select EAG.AUDIT_NUMBER Audit_Nbr, ( SELECT CASE WHEN currentstep.step_id IN (100) THEN currentstep.start_date ELSE (SELECT start_date FROM audt.os_historystep
[code].....
when I try select from this query I get ORA-00904: "DATE1": invalid identifier.
sql statement to query a transaction table that stores transactions of items bought from my organisation.The report i would like to generate is one that lists the items bought and this should be grouped month by month.
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 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'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]
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.
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),
