SQL & PL/SQL :: Group Results By Date With Running Totals?
Apr 30, 2012
My version of the Database:
BANNER
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
"CORE10.2.0.5.0Production"
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
I have a staff table with the columns staff_id and completion_date. The completion_date shows the date a staff member completed questionnaire. If the staff member did not complete the questionaire, then the completion_date column will be NULL.
Table Definition:
-- Create table
create table staff
(
staff_id number not null,
completion_date date
);
See attached text file (staff.sql) for Insert Statements.
The result set needs to have the following columns: ReportDate: the Sunday of each week. Completed: The number of staff who have completed the questionnaire by the ReportDate. NotCompleted: The number of staff who did not complete the questionnaire by the ReportDate. Total: The SUM of Completed and NotCompleted columns.
As the number of Completed goes up, the number of NotCompleted goes down. Eventually Completed will equal Total and NotCompleted goes to zero.
The result set would look similar as follows and used to generate a bar graph chart:
I have to get totals from a table using different criteria, which I do like this:
<QUERY> SELECT DISTINCT SUM(CASE WHEN MYCONDITION1 THEN 1 ELSE 0 END) AS TOTAL1, SUM(CASE WHEN MYCONDITION2 THEN 1 ELSE 0 END) AS TOTAL2 FROM TABLE1, TABLE2 WHERE COMMON_CONDITION1 AND COMMON_CONDITION2 AND datevalue1 >= DATE1 AND datevalue1 <= DATE2; <QUERY>
This works fine and I get the intended result.Now, I have to repeat this for every week for the last 12 months, excluding holidays period. So, I generate a set of date ranges which will be used in the queries. So, I repeat the above sql statement for all the date ranges, which is a lengthy process.How can I do that in a single shot and get all totals for each date range.
I am using toad to output the details of the database. (I need these details in a format which i can then import into excel) However, When I run sporadic individual records through my query, the results come back correct and as expected. But when I run the full data set through my query a lot of the records have different values than what they do when run individually!
Ive spent all day on this script and its really a hack of various other cursors and scripts brought together. Its driving me mad as my code doesn't seem to be wrong (since the results are correct when a single record is run) yet there must be something wrong as my full data set does not return correctly!
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]...
EMPLOYEEIDNUMBER(12,0) PUNCHDTM DATE TIMEZONEIDNUMBER(12,0)
I want to return any results where any employee id that has 2 different timezoneid's on the same date. I would actually like, if its possible, to select these entries to display on one row per employee per day. So for example
I need to find the closest Date that matches a Particular Date. The Closest Date from the Group may be less than or greater than the Date I am trying to find.
I have two columns: VISIT_DATE and ACTUAL_DATE. The VISIT_DATE columns has many records with different dates while the ACTUAL_DATE column would only have one record per Student ID.
Here is an example of dates:
Visit Date Actual Date ========================== 01-APR-09 19-MAR-10 16-NOV-09 19-MAR-10 17-MAR-10 19-MAR-10 21-MAR-10 19-MAR-10 04-APR-11 19-MAR-10 15-JUN-11 19-MAR-10 19-SEP-11 19-MAR-10 24-FEB-12 19-MAR-10
The closest date to 19-MAR-10 are in fact 17-MAR-10 and 21-MAR-10. I would in that case need to pick up both records.
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)
I am writing a query where I need to get the total points the student received on assignments, this is in the grade table. I then need to divide that total from the total amount of points possible, located in the assignment table. At the end all I should see is the student_id and the percentage of the students grade.
Here is where I am so far
SELECT s.student_id, SUM(g.points)/SUM(a.points_possible) "GRADE" FROM student s, grade g, assignment a WHERE s.student_id = g.student_id AND g.assignment_id = a.assignment_id ORDER BY student_id;
However when I execute this is the error I get:
WHERE s.student_id = g.student_id * ERROR at line 3:
ORA-00937: not a single-group group function The asterisk's should be under the g I couldn't get it to line up.
Serial no exp_Date exp_type exp_amount 1 01-nov-2012 Rent 10000 2 02-nov-2012 gas 250 3 02-nov-2012 insurance 9500 . . .
I want to create a sql output for a yearly view in the format
exp_type JAN FEB MAR APR ..... NOV DEC TOTAL AVERAGE PROJECTED Rent 10000 10000 10000 10000 10000 10000 120000 10000 120000 gas 250 250 250 250 250 250 3000 250 3000 . .
Now a couple of things in this
1. the average gives the average for the year, so lets say its start of 2013 and we are in feb, there will not be any values for the remaining months, so it should do the average for that exp_type for Jan and Feb based on the exp_amount entered against that type and show what is the expected average. Similary, projected will that average amount and mulitply it by 12 to show the exp amount expected based on current expenses
I was able to come up with the following sql to get the sum based on months, was not sure about average, total and projected
SELECT exp_type , SUM (CASE WHEN to_char(exp_date,'Mon') = 'Jan' THEN exp_amt END) AS Jan , SUM (CASE WHEN to_char(exp_date,'Mon') = 'Feb' THEN exp_amt END) AS feb , SUM (CASE WHEN to_char(exp_date,'Mon') = 'Mar' THEN exp_amt END) AS Mar , SUM (CASE WHEN to_char(exp_date,'Mon') = 'Apr' THEN exp_amt END) AS Apr , SUM (CASE WHEN to_char(exp_date,'Mon') = 'May' THEN exp_amt END) AS May
[Code]....
getting the correct avg, total and projected fields also in the same sql?
I'v created a report using Cross Tab in Discoverer 11g,Now I want the difference between the Amortization and Deferral as the sub totals. If you look at the attachment,I need a column below deferral column which is difference of Amortization and Deferral.
I have three blocks in my form , header,activity and item.One header can have many activities and one activity can have many items.I need to keep track of item value totals in header from items under each activity.
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 have a query where i am using the max function to find the most recent record. What i want to do is use that query as part of an insert statement into a different table, however, i don't want to insert the column that i used the max function on. Is there anyway to use the max function without having the column it is being used on showing in the results?
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 am testing a supposedly very simple stored procedure in XE. It compiles with no errors but when I enter the following command in the SQL Command window I get an ORA-00911: invalid character.
call get_all_customer_orgs;
The get_all_customer_orgs procedure is defined as.
create or replace procedure get_all_customer_orgs (p_recordset out sys_refcursor) is begin open p_recordset for select * from customerorgs; end get_all_customer_orgs;
I don't understand what the invalid character could be.
I need to select a count of records where a field (call it widget) is the same, so i need all records where widgets are distinct. So it would be like asking for a distinct in a where clause. Not having much SQL experience this is a difficulty for me.
lets see .. so a count of records where widget = widget or something along those lines.
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?
I am trying to create a query that displays the given error message if the result of my COUNT(*) is smaller than 1, but displays the result of my first query (data) if the total count is bigger than 1 (read: the query found data, so it needs to display the rows according to the search).
What do I need to do to display 'data' if 'data2' contains rows?
WITH data AS (SELECT a.order_id, a.session_id, a.log_id, b.date_of_order, a.operation, b.funct_prod_code, b.sts_status_code, b.ost_order_situation_code, c.order_situation_oms,
