Total Balance (sum Previous Row With Atual Row)
How to make a select table that give some results like this:
INCOME | OUTCOME | BALANCE
1000000 0 1000000
1000000 0 2000000
500000 0 2500000
0 1000000 1500000
Is it possible?
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Running Balance With Where
I am using the following query: SET @bal1=0,@bal2=0,@bal3=0,@bal4=0,@bal5=0,@bal6=0; SELECT Id, Date, FCode, Descr, Debit, @bal5 := @bal5 + DebT AS RunDeb, Credit, @bal6 := @bal6 + CreT AS RunCre, @bal2 := @bal2 + net AS RunBal, Rec FROM ( select fin_records.id, fin_records.id_production_unit as PUnit, fin_records.id_bank_account as BAcc, date_format(fin_records.fin_record_date,'%d-%m-%y') as Date, fin_accounts.fin_account_code as FCode, fin_records.fin_record_desc AS Descr, fin_records.fin_record_debit AS Debit, @bal3 := fin_records.fin_record_debit AS DebT, fin_records.fin_record_credit AS Credit, @bal4 := fin_records.fin_record_credit AS CreT, @bal1 := fin_records.fin_record_debit - fin_records.fin_record_credit AS Net, fin_records.fin_record_rec AS Rec from fin_records inner join fin_accounts on fin_accounts.id=fin_records.id_fin_account where (fin_records.id_production_unit='$up') and (fin_records.id_bank_account='$ba') and (fin_record_date between makedate(year(curdate()),1) and makedate(year(curdate()),365)) order by fin_records.fin_record_date,fin_records.id ) AS tmp; I should build another column to get RunBal2, that should include only records with Rec = 1.
Running Balance
I have to calculate running balance in a query, I am trying this: set @fin_record_bal1=0; set @fin_record_bal2=0; select id,fin_record_debit,fin_record_credit,@fin_record_bal1:=fin_record_debit-fin_record_credit,@fin_record_bal2:=@fin_record_bal1+@fin_record_bal2 from fin_records The above doesn't work in record one
Balance Performance
My computer is AMD athlon 64 3400+ with 1.5G DDR. when i execute the following sql in mysql 5.XX. select c.filename, c.topic from articles c where c.topic in (select a.topic from articles a group by a.topic having count(a.topic)>1) order by topic; It eat up all the cpu power , and the mysql server stop responsing until end of this query. Even my apache stop responing too. How to fix this? How can i set the CPU limit of each query connection?
What Is The Effective Way To Query Balance?
I have 2 tables, payment and receive, i want to retrieve the balance for a user. mysql> select * from payment; +-----------+--------+---------+ | paymentid | userid | payment | +-----------+--------+---------+ | 1 | 1 | 2.0000 | | 2 | 1 | 2.0000 | | 3 | 2 | 2.0000 | +-----------+--------+---------+ 3 rows in set (0.00 sec) mysql> select * from receive; +-----------+--------+---------+ | receiveid | userid | receive | +-----------+--------+---------+ | 1 | 1 | 1.0000 | | 2 | 2 | 1.0000 | | 3 | 2 | 1.0000 | +-----------+--------+---------+ 3 rows in set (0.00 sec) I want to know, how do you deal with this situation? If I want to get the balance for userid = 2, I have to get the sum of the payment, and minus the sum of the receive. So it will execute 2 sql query for getting balance. Another approach would be creating another table, called balance. When a new payment being made, the value will be added to `payment` and modify the `balance`. likewise for receive. mysql> select * from balance; +-----------+--------+---------+ | balanceid | userid | balance | +-----------+--------+---------+ | 1 | 1 | 3.0000 | | 2 | 2 | 0.0000 | +-----------+--------+---------+ 2 rows in set (0.00 sec) Which method will you use? or do you have better idea for this?
Select Previous And Next Id After Id=# ?
For a gallery I'm trying to query for a picture by its id and also get some images before and after that id (according to current sort) are there references like DOM previousNode or stuff like that to use in the WHERE statement? Or how could I do this? Currently I have: ($picId is a php variable) '(SELECT id, picUri, picUriSmall FROM pictures WHERE id < '.$picId.' ORDER BY id DESC LIMIT 2) UNION (SELECT id, picUri, picUriSmall FROM pictures WHERE id = '.$picId.') UNION (SELECT id, picUri, picUriSmall FROM pictures WHERE id > '.$picId.' LIMIT 2)' This works but doesn't seem to be the best way. I want to be able to have it working for other sorts than by id. And the two first images gotten by this query will be in reversed order (can be fixed in php). Is there a way to ask for my picIDs position in a query and then use another query to make the LIMIT 10,14 if my image was in position 12?
Paging With Only Next/previous
I'm running PHP + MySQL (innodb) and in some pages, the paging is slowing down the system a lot. Now I've seen on some sites that have paging implemented in an interesting way. They only let you go forwards and backwards one page at a time, and some don't even tell you how many results there are. For instance if you look at google search results, they only says "about XXX results". Plus they never actually let you jump to the last page. I'm thinking of implementing my system this way in certain pages as well, to cut down on the load times. However, I'm exactly sure how that cuts down the load. Can someone shed some light on this?
Previous To Last With A Certain WHERE Condition
How do i fetch the previous row record with the same WHERE when the last record has only a lately date. for example field CConsorcio CRubro ImporteA FechaA 1 105 1000 2006-05-10 2 110 30 2006-05-12 5 105 300 2006-05-13 4 104 600 2006-05-14 2 103 800 2006-05-15 the WHERE CConsorcio=2 the last record: 2,103,800,2006-05-15 and i want to select the row: 2,110,30,2006-05-12
Using Data From Previous Row
I only want to select the current row if a field in the previous row is not null. Is it possible to write such a query?
Previous Record
I need to get a previous record from database - i need to retrieve records which differs from the previous one about 5s example: select eventDate,previousEventDate,eventDate-previousEventDate as delta from events having delta<5 order by eventDate; eventDate is normal column from table previousEventDate is an "alias" for the previous date ... how can I write it in SQL? Please help. I can use SELECT as subquery, but it would be probably really slow, the table will have 100000s maybe milions of records soon.
Previous Row Comparison
I have a table in this format: Column A | Column B 1 | 2 1 | 3 1 | 4 2 | 5 ..... ..... What I want to do is something like this: If in Column A there is a change in the number i.e. from the above table there were many 1s which transitioned into a 2. I want to record this change. I want to extract these two rows into another table. How can I make this possible?
Get Next And Previous Records
Our website has a portfolio database which uses a column entitled 'list_order' to set the order of how we want the projects to display in a list. Each project has a link to click into it to see the full details of the project. Inside this details page we would like to add a View Previous Project and View Next Project link which would allow the user to navigate through our projects. The problem is that we are using an order set by 'list_order' and not an auto incremented column. How would I setup the Query to grab the previous and next rows no matter which project the user clicked on? For example, our database has these records: id - list_order 102 - 100 89 - 200 7 - 250 30 - 300 97 - 400 20 - 500 62 - 600 88 - 650 103 - 700 85 - 800 112 - 900 95 - 1000 So, if someone clicked into id 97, we'd need a query that would grab id numbers 30 and 20 for the previous and next id to create the links.
ID Of Previous Insert
Im thinking i need a transaction here. I want to insert a record into a table, which has an autoincrement field as the primary id. Then use this id to insert into another table. In the time between the first insert and then getting the id, couldnt there possibly be another user that causes an insert and hence the id the first user gets back isnt correct ?transactions to the rescue ?
Subtract Previous Value.
I'm trying to get one column of numbers to subtract from another column and to keep a running total. Column A is a dollar amount from an invoice. Column B starts out with an opening amount ($10,000) and I want to have Column A to subtract from that. Code: COLUMN A--------COLUMN B $0.00 $10,000 $1000 $9,000 $500 $8,500 $2000 $6,500
Get Previous And Next ID Of Current Record
I want do create a "Previous" and a "Next" link, all I have is the ID of the current record. Is there a good way to get the previous and next id of a record with a single SQL Query? example: ID's: 4,10,7,9,13 I open my page and show all details from the record with the ID 7, now I want to create the links and I need the previous and the next ID, 10 and 9. I could make a query that returns only the ID's and loop through the recordset till i have my ID's but that's such an ugly solution, not to mention the performance issues.
Phase Previous And Next Record
I want do create a "Previous" and a "Next" link, all I have is the ID of the current record. Is there a good way to get the previous and next id of a record with a single SQL Query? example: ID's: 4,10,7,9,13 I open my page and show all details from the record with the ID 7, now I want to create the links and I need the previous and the next ID, 10 and 9. I could make a query that returns only the ID's and loop through the recordset till i have my ID's but that's such an ugly solution, not to mention the performance issues.
Previous And Next Record Delimma
I have a main page that has a query to get all my clients in alphabetical order. "SELECT * FROM clients ORDER BY cname ASC" This obviously mixes up their IDs. So when I click on a client's name "Goofy" (id = 6), I need the next client "Mickey Mouse" (id = 2), and "Donald Duck" (id = 9) to be the previous client. And I would prefer this to happen on the same page where it displays my client's details rather than have to put those vars in the URL. To get my client, it's simply: $client = $_GET['c']; "SELECT * FROM clients WHERE clientid = '".$client."'"
Getting Next, Previous Record After INSERT
When I insert a record in a given index order I want to be able to traverse to the previous or next record of that index order, before or after the one I inserted. So for example if I insert a record with an address of "20 Heather Court" using an address index order, I want to be able to push a Next button (on a Web HTML form) to go to the record corresponding to "21 Heather Court" or, if I press the Previous button I I want to be able to go the record corresponding to "19 Heather Court". Assuming of course that both the Next and Previous records exist before I insert "20 Heather Court". If I change the index order to customer first name and insert a new record for a customer named "Harry" I then want to be able to push the Next button and see the record for a customer named "Jan" or if I push the Previous button I likewise want to pull up the record for a customer named "Garry". Assuming that the records for Jan and Garry already existed before I inserted the one for Harry and that they became the Next and Previous records with respect to Harry's after the insertion. Using an auto increment field does no good because every time I change the index order any kind of sequential relationship between the values in the auto increment field gets totally messed up. I have also tried statements like " SELECT * FROM Customers WHERE Customer_ID <349 ORDER BY `Customer_ID` ASC LIMIT 0 , 1" without success if there are 10 records before the one with a Customer_ID of 349. A LIMIT of 0,1 will start at record 0 for only 1 record. I want to pull up the record immediately preceding the one with a Customer ID of 349. In short is there some way to tell which record is Next or Previous to one that I insert under a given index order?
Subtracting Current Row From Previous
I am trying to create a query that will subtract the current row of a column from the previous row, and if the value is negative, just return the value of the current column in the result column. For example, if I had the table below, here are the results I would like to see: Key value REQUIRED RESULT 1 12 12 2 21 9 3 3 3 4 1 1 5 67 66 .
Previous Version Coding
I am trying to load some sql into myphpadmin and whilst I believe I have a later version of 5.0 which is causing the error (as this coding is apparently up to version 5.0), can someone suggest how I go about finding the coding to use with my version? Here are some of the errors: CODESET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"
Determine Previous Month
basically I need to display a list of "unpaid" statements for the previous Month, no matter what day the search/request is made on. I know MySQL has the DATE_SUB function where I can subtract 1 Month from the current date, but will that subtract the date exactly 1 month to the day ? Or will it simply give me the Month as I need ? What happens if the search takes place on the 31st of a month (for example: July), yet the previous month only has 30 days (June). Will it roll back to the previous month correctly or think that the date is 31st June or try to roll back further until it finds another month which has 31 days ?
Previous Versions Of MySQL With XAMMP
I've downloaded XAMPP for Linux 1.5.1 for use on Fedora Core 4. I notice it only has the latest version of MySQL on it. I'm working on two web sites (for different clients) and they are hosted on different shared Linux hosting servers (with PHP), each with different versions of MySQL available, i.e: MySQL 4.0.25-standard MySQL 3.23.52 Is it possible for me to use older versions of MySQL with XAMPP? Could I just turn off MySQL in XAMMP and just run and older version of MySQL independently of XAMMP? If I can then how would I install multiple versions of MySQL in Fedora Core 4?
Getting Data Entered In The Previous Week
When a product is added to the database it has three seperate fields storing the date added in(01 - 31 format) month (January-December) and year( xxxx). Is there anyway that I can run a mysql/php query that will just retrieve those products entered within the last xx weeks?
Compare Previous String In Resultset With Next
The code below does not work. while(resultSet.next()) { if (!resultSet.getString.next().equals(resultSet.getString.previous()) { out.print("..."); } } Is it possible to compare previous string in a resultSet with the next string? If so, could you please show me an example.
Query To Find Data For Previous Week
I am trying to get a query that can find if a Loandate falls in last week...so basically check if loandate is between sunday or monday of last to friday or saturday of lastweek... Any clues: Select * from loan where last weeks sunday<Loandate>last week's saturday.
Selecting Previous And Next Rows Based On One Input
let me start by what i am doing: SELECT msgid FROM messages WHERE uid=1; output: +----------------------------------+ | msgid | +----------------------------------+ | 1588ad275a80f22e6206364abbba530a | | 37ce77bea4481ab7ed2625055512467e | | 4bf7e4cb13590da24625821c795eb8d7 | | 57628792ba507161e3f73daff3c972b7 | | 91e95b3fbe9f04467023cfe84e6ed6e2 | | e710bb38be4f3cbd05b6db594f7e8805 | +----------------------------------+ how would i get it so if i enter a value it will return the previous and next rows. SOME_SQL WHERE msgid='57628792ba507161e3f73daff3c972b7' AND uid=1; output: +----------------------------------+ | msgid | +----------------------------------+ | 4bf7e4cb13590da24625821c795eb8d7 | | 57628792ba507161e3f73daff3c972b7 | | 91e95b3fbe9f04467023cfe84e6ed6e2 | +----------------------------------+
Get Previous And Next Records Relative To An Index Record
Suppose I have a table "product" with fields id,name,price. I want to get a resultset of previous and current and next record when the resultset is sorted by id and id=10. The table data is shown in the following: id Name Price 1 : : 5 : :<--------------previous record 10 : :<--------------id=10 12 : :<--------------next record 14 : : How do I write the sql statement if I do not know 5 and 12 are the previous and next id respectively?
How To Insert New Records Based On Previous Record Values?
I have TABLE: year value It has for example, year, code, vlaue 2007, 33, 4883 2006, 33, 4883 2007, 32, 4883 2006, 32, 4883 2008, 31, 4883 2007, 31, 4883 2006, 31, 4883 I want to insert new records for for 2008 for all codes that do not already have a record for 2008 and increase the value for the 2008 record by 3%. My several attempts have led to this but which does not work. UPDATE TABLE a SET a.value = b.value * 1.05 WHERE b.year = 2007 AND code NOT IN (SELECT code FROM TABLE b WHERE year =2008 AND value !=0)
Update Query Fails In Previous MySQL Version
The following query runs fine on my development server (v4.0.17): "update user inner join events on user.username = events.username set user.LastMessageDate = '$current_time' where events.text_sent=0 and events.to_mobile >0 and events.GMT_event_send_time<='$send_time'" but fails on my production server running v3.23.56. Anyone any ideas why it doesn't run and what I need to do to fix it?
Total Sum Of Values?
this isn't a question of "how do I do this" but more of a "what's the best efficient way of doing this" the current code is: $result=mysql_query("SELECT votes FROM list"); votes is always a numerical value what's the most efficient way to add the total value of the votes?
Total Export In SQL
I need to have a total backup in SQL commands of a SQL-driven datasource. I mean, data and structure. I know that phpmyadmin can quickly deliver those means in a lone command, and of course I could backup the entire base if I only had phpmyadmin or a remote access mounted on the datasource. Simply, I dont have that and the customer's situation wont change. I only have my php and ftp access, in order to input some query into the datasource, so it pumps me all the data and structure.
Sub Total Query
I am pretty new to this stuff. I am trying to total the quantity colum in a table but on for each group. The quantity contains multiple instances of on certain part number and I group the results by part number. I need to get a total quantity from this. Here is what I have....
Rows And Total
I would like to return the following recordset and also get the total without having to run through the table first.. possible? $query = "SELECT SUM(h.Time) as projecttime, h.Date, h.Time, h.Message, c.No as CNo, c.Customer, p.Title, p.No as PNo FROM projects_history as h RIGHT JOIN projects as p ON h.Project = p.No RIGHT JOIN customers as c ON p.Customer = c.No WHERE h.Date BETWEEN '$thestart' AND '$theend' AND h.Deleted = 0 AND h.Time > 0 AND h.User in ($theusers) AND (c.No = $customer OR $customer = 0) AND (p.No = $project OR $project = 0) GROUP BY c.No, p.No ORDER BY c.No, p.No ASC"; $res = @mysql_query($query) or die(mysql_error()); while($row = @mysql_fetch_assoc($res)){ $totaltime += $row["projecttime"]; } $res = @mysql_query($query) or die(mysql_error()); while($row = @mysql_fetch_assoc($res)){ ... }
Counting Total Row
I am trying to select the total row count from two different tables. I am getting a result back but it is returning the same count for both even though they have different amounts. This is what I have so far:- SELECT COUNT(companies.comp_id) as tcustomers, COUNT(project_id) as tprojects FROM companies, projects as you can see I have two tables, companies and projects.
Grand Total
I have a site (php/mysql) which people register on, say how many people work in their firm and how much volunteering they are going to do by quarter. The relevant fields are q1,q2,q3 and q4 (the four quarters they volunteer for) and staff (the number of volunteers). The code I have is: SELECT firm, q1+q2+q3+q4 as total FROM hours INNER JOIN contact ON contact.username=hours.username Now what I want to do is divide the total I get for each firm by the number of staff. Also, I would like to find the grand total by adding all of the totals.
Total Up Fields
Is there an easy way to do a query that will total up fields froms several different records? For example... If my table has this data... ID AMOUNT 1 45 2 51 3 9 4 17 5 15 I want to be able to do a query that will add up all the numbers in the "Amount" column. Can this be done?
Total Numbers
I need to count total number of records present in 2 tables. I am having a working query for SQL Server but it doesn't work in mySQL. select sum(a.total1) from (select count(*) total1 from CoreJava_Code union all select count(*) total from CoreJava_File)a
Calculate Total Using MySQL Or PHP
Can someone recommend a the better of two ways of calculating a list of product totals: By using the following SQL: ######################################### # Get Product, Pricing and Taxes Amounts # ######################################### SELECT PRDS.prd_product_name, PRCS.prc_base_price as Price, TAXS.tax_tax_name as Tax, (TAXS.tax_tax_amount * PRCS.prc_base_price) as Tax_AMNT FROM lop_lists_of_products as LOPS, prd_products AS PRDS, prc_prices AS PRCS, map_prices_to_taxes AS MP2T, tax_taxes AS TAXS WHERE LOPS.prd_product_id = PRDS.prd_product_id AND LOPS.lop_list_of_products_id = 1 AND PRDS.prc_price_id = PRCS.prc_price_id AND MP2T.prc_price_id = PRDS.prc_price_id AND TAXS.tax_tax_id = MP2T.tax_tax_id; I generate the following result set: prd_product_name PriceTax Tax_AMNT test Product 1 54.9900G.S.T3.2993999262527 test Product 1 54.9900P.S.T4.3991999016702 Test Prod 2 27.5500G.S.T1.6529999630526 Test Prod 2 27.5500P.S.T2.2039999507368 As you can see the two products each have G.S.T. and P.S.T. taxes. If I want to get a total price that includes both taxes would I be better to:Use PHP get the Price and the Tax amounts and add them together? OR;Is there a way to get this total using SQL, and if so is it better to use SQL for this type of result or even though a method may exist to calculate totals (the way in which I am asking) the processing overhead is too great and PHP doing the calculation would be a better way?While I know how to perform the calculation in PHP, I don't know where to start to take the existing result set (shown above) and then perform another calculation upon it using SQL.
Calculating Total Time
I am using MySQL 4.0.17-standard on W2K and I am having trouble with a time management application. It's actually very simple, I have 2 columns TIME_IN and TIME_OUT. They are both time fields, and i need to get a TOTAL_TIME column from a query. I know that there is a TIMEDIFF function in MySQL 4.1 but I am not in a position to upgrade right now. This is a quite simple task and I imagine that there is way to do it, I just can't figure it out.
LIMIT (total Records)
If I have a query with LIMIT what will the best way to find out the total number of records returned by the same query but without any LIMIT? Will it be different solutions for different versions of MySQL? Is that possible to get this number in the same one query with LIMIT?
Getting The Total Size Of Many Fields
I've got text infos in one of my access column. I know the size of a precise text length by using the len() function in ASP, and this size is recorded in a size field (si I get my size in bytes). Now, I would like to add all the sizes I have to make a total of them. I don't the sql code to do that I've been trying this: select Count(size) as totsize from mytable where id=" & myID <%=rs("totsize")%> but it obviously gives me the number of time there's a size int for the requested id, and not the total of all thoses sizes (well, I'm not very clear in my explanations, but I guess the code speeks for itself). What would be the sql line to get all the number of my size column for a precise id, and then have the total of all thoses numbers? btw, 1000 bytes make 1k right? No tricks about it right?
Getting A Total Of A COUNT Column
I am building a simple poll system and part of this is a query to pull the poll results from a MySQL table. The system contains 3 tables. pollquestions - Contains just an id and question string. polloptions - stores options for each question. contains id, questionid and a string for the option pollanswers - contains the 'votes' cast in each poll. Contains and id, question id and option id. To get the results data for a poll I'm using the following simple query which seems to work OK. (in this case I am grabbing the results for poll '2') CODESELECT COUNT(*) as votes, polloptions.id, polloptions.option FROM pollanswers LEFT JOIN polloptions ON polloptions.id=pollanswers.answerid WHERE pollanswers.questionid=2 GROUP BY pollanswers.answerid
Creating A Column That's The Total Of Others
i'm trying to create a table in which there is a column that has the total of two other columns, for example: ---------------------------------------------------- | item | morning | afternoon | total | ---------------------------------------------------- | oranges | 56 | 20 | 76 | | apples | 23 | 15 | 38 | ---------------------------------------------------- the values for morning & afternoon for each item would be incremented by the program / user, but the values in the total column should update automatically to be the total of morning & afternoon so that when requested you get the total rather than having to request the separate values then add them up, is an entry that updates itself something that MySQL can do? and if so what is the name / keyword that i should be looking for to find out how to do it?
Sum Total Of A Time Field
We have a table with a field of type TEXT storing the time for certain actions (ie. 0:30 - for 0 mins 30 secs, 1:20:00 - for 1 hr 20 mins 0secs). We've come to a point where we'd like to sum the entire field up and get back the total time. I wish we established this filed as a time data type rather than TEXT but that's the way its set up. How would you go about achieving this? Perhaps I need to convert the TEXT to some other format to get back a more usable value for my PHP code to use?
Total Number Of Items
I am limiting a query to only one line to save code in perl because that's all I need. However, is there a way to have MySQL tell me how many lines there would have been without the limit and without running another query? By limiting, I can't use scalar(@lines); in perl. I'm sure Mysql has a way to calculate lines, but I can't seem to find it.
Decrypt A Total Table
I have created a function to readout values form a table. function GETPROPDATA($prefix) { $Q_prop = mysql_query("SELECT * FROM ".$prefix."prop"); while($r = mysql_fetch_array($Q_prop)) { $dataholder = $r['prop_variable']; $return[$dataholder] = $r['prop_value']; } return $return; } however the value data is encrypted with a AES layer. is it possible to decrypt every value without having to define eatch and everyone of them?
|
TRACKER
