Using Subtract & SUM in the Select Statement

I am trying to use addition and subtraction in a select statement. The query below does not work. Does anyone have any suggestions?Thanks in advance!select a.legcramt-sum(b.paidamt) as balancefrom tb945 a,tb949 bwhere a.eid = b.eid ...

MD5 Sum lookup in the Select Statement

After reading all the warnings against using simple things like ids in the url, is there a way to make a select statement do a MD5 sum on the value in its database and check it against a already md5 sum? If that was confusing, let me put it this way.insted of url.php?id=1, use url.php?id=c4ca4238a0b ...

Select statement to select sum of only few columns

Respected Friends HiI have one table named as 'payroll' with the columns employee_id,employee_name,employee_designation, basicpay,allowances,month,year. This table data as follwoing:employee_id , employee_name, employee_designation, basicpay , allowances , month, year1 , SAM , MANAGER , 5000 , 1000 ...

I need select: total sum from two tables

I have two tables with I have to make summary select statement.Table1:Sales12Table2:Sales34I would like to write one SQL withc would return the sum from bouth tables that is: 10.How to write such SQL? ...

Inconsistent results with "UNION"ed Select Statement

I have 2 select statements with UNION in between - both have GROUP BYGROUPING SETS to roll-up column totals. Sometimes I get the 2 total lines(one for each select) at the very end of the results, other times, the firsttotal line appears after the first "select" detail and the other appears atthe end ...

Get the SQL0104N error with SQL select statement

I'm try to use the following SQL select statement and keep get the error SQL0104N? could anyone tell me what is wrong.SQLString="SELECT Sum(elapse_time) AS elapse_time, Sum(accepted_card) AS accepted_card, (elapse_time / accepted_card) AS meantime, status From TABLE_A Where char(int(date(proc_Date)) ...

Simple Select Statement

I can't seem to get the following select statement to work.$GetData = mysql_query ("select year,SUM(wlt='W') AS winfrom gameswhere team1 = '$team' and win > '9'group by yearorder by win DESC");I think the problem has to do in the where statement with the (and win > '9') when I try to ...

Oracle A select statement

I have a select statement that i got from some sql book. Please let me know how you can get this to work on Oracle.Select rep, cust, amount From ORDERSorder by rep, custcompute sum(amount) by rep, custcompute sum(amount), avg(amount) by rep;I await a favourable rejoinder from you all, ...

Calculations in select statement

I try calculated total of Units.In first placed my select statement was select *, sum(cast(UNITS as INT)) over (partition by b_alt_id order by person, dos_dt )AS TOT_UOSfrom Report4_results.But in SQL partition by order by is not work.I looking for calculation:Person, Serv Dt, Unit, Total001 ,1/10/2 ...

Help for newbie with Select statement

I am having trouble using the select statement to give me a running total from one table in my database. Here is an example:Price cost_fee units$200 15% 5$150 10% 4$670 15% 8I am trying to get a running total amount for profit. I have been trying things like: sum(price-cost_fee) * units from money_d ...

select statement query

all, i have a select statement which looks like thisselect dealer_id, sum(sum_f1) sum_f1, sum(sum_f2) sum_f2, sum(count_f1) count_f1, sum(count_f2) count_f2 from f01_dealer_balance_to_user where (sum_f1 <> sum_f2 or count_f1 <> count_f2)and (TO_DATE(reload_date,'DD-MM-RR') >= '24-MAR- ...

Select Statement

I need to put a select statement together that would display the list of columns as well as the sum of the values in them. I wrote two seperate select statements that accomplish that but not sure how to combine them. Here are the selects that I wrote:select sum(length((table2.col1)||') '||upper(tabl ...

Oracle Inserting values thru a select statement which contains functions

In a Reports Before-Report trigger, I am inserting values into a table thru a select statement which contains a function. But the values returned by function is not proper. But when I fire the insert from oracle or toad it gives proper values.Insert into table(x,y,z) select X,sum(Y),Func_Z(max(a),:b ...

select statement

I have the following select statement:-select customer,warehouse,sum(CASE DATEPART(yyyy, dated)WHEN 1999 THEN val ELSE null END) as y1999,sum(CASE DATEPART(yyyy, dated) when 2000 THEN valELSE null END) as y2000 from sales table where warehouse='E' or warehouse='EC'group by customer,warehouseorder by ...

Oracle Logic problem with select statement

Can anyone help me?How can I make the following select statement pull ALL inventory records regardless of whether there were sales or not. This statmement only pulls orders that were active on whichever date is referenced as the SHIP.DTI would prefer for ALL items to show up with a '0' for 'SALES' i ...

MySQL Select statement with multiple joins and unions on tables with approximately 2 million records each....improve performance?

I have a select statement with multiple joins and unions on tables with approximately 2 million records each. I have all the indexes I can think of. The keys are as small as they can be. I have run analyze table on all the tables. I can't think of anything else to try. The SQL takes approximately 40 ...

can I select the SUM of columns and...

Can I select the SUM of columns in my database as well as select the number of rows in the database (ie. mysql_num_rows)?This is my sql statement:$sql = "SELECT SUM(cash) AS cash_total,SUM(visa) AS visa_total,SUM(mastercard) AS mc_total,SUM(amex) AS amex_total,SUM(gst) AS gst_total,SUM(pst) AS ...

Oracle Performance Tuning Help : Repeating HUGE Select Statement...

I have a select statement that I am repeating 12 times with the only difference between them all being the date range that is grabbed. Essentially, I am grabbing the last 12 months of information from the same table here is a copy of one of the sections:(selecta.account_id as account_id,a.company_id ...

Oracle ERROR in SELECT STATEMENT

I am trying to write a fairly simple SELECT statement and I keep receiving an error. This is what I have:table_name := 'SYSADM.ETS_Y'||TO_CHAR(:YEARlist)||'_W'||week||' A';find_table := 'ETS_Y'||TO_CHAR(:YEARlist)||'_W'||week||''; select month into month_noFROM '||:table_name||' where rownum = 1 ;I ...

Oracle The Query Very fast, if i include this select statement, but remove...very

I need 2 types of reports Daily and Weekly.In Weekly Report ( This query is faster)select ModelNo,sum(decode(trunc(m_date),d1,mcount,0))"SAT 09/10/2005",sum(decode(trunc(m_date),d7,mcount,0)) "FRI09/16/2005" from model a, modelid b,(select to_date('9/10/2005','MM/dd/yyyy') d1,next_day(to_date('9/10/ ...

Oracle Help with a select statement

I'm having trouble getting the correct select statement for my report.For a simplified example,CREATE TABLE temp1 (my_fk number);CREATE TABLE temp2 (my_fk number, t2 number);CREATE TABLE temp3 (my_fk number, t2 number);INSERT INTO temp1 VALUES (1);INSERT INTO temp2 VALUES (1,5);INSERT INTO temp2 VAL ...

Oracle Select statement not working when used in a procedure

I have a simple select statement that runs fine in the sql prompt of sqlplus, but when it is used in a cursor...for loop in a procedure, it throws an error at run time. The execution in command-line sqlplus is shown below: SQL>SQL> @verBANNER Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64b ...

Oracle select statement taking lot of time

I am working for telecommunications client.I am having a table with columns day,hours of call,tariff class,amounti am having a select statement select day,tariff class,sum(amount ) from xx where to_char(sysdate,'mon')='OCT' group by to_char(sysdate,'mon').below is the samples of the data.DAY HOUR TA ...

Oracle Issue with Past Month data in the Select Statement

I written the following query,SELECT /*+NO_MERGE(A)*/ CASE WHEN snap_shot_date > a.q3 AND snap_shot_date <= a.q4 THEN q4char WHEN snap_shot_date > a.q2 AND snap_shot_date <= a.q3 THEN q3char WHEN snap_shot_date > a.q1 AND snap_shot_date <= a.q2 THEN q2char WHEN snap_shot_date > ...

Oracle Index not being used in Select statement

FriendsI have the following SQL statement:SELECT a.acct,a.date_field,UPPER(b.feegroup) feegrp,SUM (a.fee1) fee1,SUM (a.fee2) fee2,SUM (a.fee3) fee3FROM table1 a, table2 bWHERE 1 = 1AND a.fee_id = b.fee_idGROUP BY a.acct, a.date_field, b.feegroup;Both the tables have index on fee_id column. When I ru ...

Oracle write as a single sql select statement

Need to help for write as single sql statement instead of 4 below stmt'sSELECT CONSUMER_KEY, SUM(SUM_OF_AMOUNT) SUMAMT FROM SERVICE_FACTS_BY_MONTH WHERE MONTH_KEY = 20100201 AND SERVICE_KEY =0 GROUP BY CONSUMER_KEY ORDER BY SUMAMT, CONSUMER_KEY;   SELECT CONSUMER_KEY, SUM(SUM_OF_AMOUNT) SUMAMT ...

Oracle Can't figure out the correct syntax for this select statement

The following statement works great and gives the desired results:prompt prompt Using WITH t prompt   with t as ( select a.proj_id, a.proj_start, a.proj_end, case when ( select min(a.proj_start) from v b where (a.proj_start = b.proj_end) and (a.proj_id != b.proj_id) ) is not null then 0 else 1 ...

MySQL User variables in a select statement

The following select statement works fine. I would like to improve it in three ways 1 by using variables in the select statement so that lengthy lines of code are not repeated. And also so that the values for last year (2010) are not hard wired into the query 2 by adding a totals line at the bottom ...

PHP User variables in a select statement

The following select statement works fine. I would like to improve it in three ways 1 by using variables in the select statement so that lengthy lines of code are not repeated. And also so that the values for last year (2010) are not hard wired into the query 2 by adding a totals line at the bottom ...

Insert into select statement using 2 tables

I have employee table for attendance and i have a test select statement to get the total hours of employee:select sec_to_time(SUM(unix_timestamp(timeout) - unix_timestamp(timein))) AS totalhours from employee;and now i want to insert the sum of hours per employee in time database with the employee n ...

Insert into select statement using 2 tables

I have employee table for attendance and i have a test select statement to get the total hours of employee:select sec_to_time(SUM(unix_timestamp(timeout) - unix_timestamp(timein))) AS totalhours from employee;and now i want to insert the sum of hours per employee in time database with the employee n ...

Insert into select statement using 2 tables

I have employee table for attendance and i have a test select statement to get the total hours of employee:select sec_to_time(SUM(unix_timestamp(timeout) - unix_timestamp(timein))) AS totalhours from employee;and now i want to insert the sum of hours per employee in time database with the employee n ...

PLS-00428: an INTO clause is expected in this SELECT statement

I am creating a stored procedure for gathering table stats and am getting an error on my select statement but I don't see what the problem is? Any ideas?select 'dbms_stats.gather_table_stats(ownname => user,tabname => '||table_name||',estimate_percent => 2,block_sample => TRUE,cascade = ...

Scalar function with SELECT statement

I'm new to DB2 - all my experience is in Oracle. I am trying to write a function that will return the scalar result of a SELECT statement:CREATE FUNCTION myResult(inID INTEGER) RETURNS CHAR(6) LANGUAGE SQL READS SQL DATA NO EXTERNAL ACTION NOT DETERMINISTIC RETURN SELECT name FROM myTable WHERE id = ...

select statement

why can't I perform a query of the bellow pattern :select field1,field2 from select_statement1,select_statement2.note:select_statement1/2 are select statement which pruduce valid tables. ...

How do I return 10 results per page using SELECT statement?

I'm wasn't sure whether to post this under the PHP or under the MySQL section of the forums. Since most people (I'm assuming) who write PHP script, also use MySQL, I thought I'd stick with PHP. I'm rather new to both PHP and MySQL and I'd be very grateful to anyone who can help me solve a problem I' ...

Dynamic sql in DB2 Cursor select statement

I want to pass a dynamic select clause in the Cursor FOR SELECT statement. e.g,DECLARE carrier_cur CURSOR WITH RETURN FORSELECT carriername, effectivedateFROM Carrier;Instead of specifying the two fields at design time, I want it to be framed at runtime where I have the options to select the fileds ...

MySQL syntax error on select statement

select request_no,ctime from request_no_tb where ctime=(select max(ctime) from request_no_tb); request_no int ctime datetime Can anyone explain why I am getting a syntax error on this select statement. Thanks select request_no,ctime from request_no_tb where ctime=(select max(ctime) from request_no_t ...

y index need for select statement

I need the index is useful for regular select statment?If i put select count(*) without index it takes more , but if i have one index in that, it fetches the rows very quickly and both statements does not have any where clause.so pls tell me db2 needs indexes for regular select statment.My coworker ...

MySQL how to append string of column values in SELECT statement?

expert out there. I'm looking for syntax to append string of column values in SELECT statement. For example, following is a SQL statement to select the property_address1, property_address2 and property_address3. The values should be returned in accordance to their respective columns. SELECT property ...

Timestamp of a SQL select statement

Does DB2 store any where the timestamp of SELECT statement? Basically I want to know when a particular SELECT statement was run last.. ...

Select Statement: Limiting Number Of Rows

how can i do to limit the number of rows retrieved in a select statement?For example in sybase there is "set rowcount" command. ...

SQL Select statement and LEFT OUTER JOIN and 3 tabls, please help =(

SQL is definitely not my strong point. I need to add a column to an existing select statement that looks like this:--------------SELECT A.ID, A.SCHEDSTARTDATE, A.DBID, B.NAME, ...about 12 more columns all from the A table...FROM FirstTable A, SecondTable BWHERE A.STATE = B.ID-------------I have to j ...

Select statement in DB2

I have 2 tables: TAB1 incliudes PRIMARY_PROD_NO and ALT_PROD_NO TAB2 has PROD_NO (as KEY) and PROD_DESCRIPTION.How do I code a select statement that will give the result set of:PRIMARY_PROD_NO, description, ALT_PROD_NO, Description?Eachh Description relating to each Prod_no. ...

Number of rows in the select statement

I am doing a select select statement through JSP. I wan't to give number of rows in the result...For eg.. String s1 = "SELECT id, f_name,l_name FROM login WHERE username = '"+ username +" '";I want to display the number of rows returned....How do I do that....I tried...int rowsUp ...

How to write SELECT statement with AVERAGE formula

I would like to write SELECT statement which calcuates the average quantity for last 3 weeks for each week in year 2004 for each productid (please see the formula at the bottom).Thanks,Grofaty====== Detailed source data ====== Week ProductID Quantity 200352 1 130 200352 1 120 200353 1 100 200353 1 1 ...

Select statement

Looking for a statement that will prevent doubles from showing up in a search.If I have a SQL select statement that is reading 2 tables that may have the same records. Is is possible for me to search for a name pon multiple tables and only display it once in the results? ...

How to select to tables of two data bases in a SELECT statement?

How to select to tables of two data bases in a SELECT statement? I have two data bases, each db in a different server. ...

Need help on a select statement using MYSQL ^.^

I want to create a select statement showing --> John -- happy -- Alice -- Mad -- Sunday John -- Mad -- Alice -- happy -- Monday Here is my table structure: main (table) main_id int int primary key person_id_1 int references person(person_id) mood_id int references mood(mood_id) person_id_2 int re ...

To find the no. of rows affected by a select statement

I would like to know the number of rows affected by a select statement.. I tried with 'GET DIAGNOSTICS', but it does not work..any other way to do this.. ...