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....
View Complete Forum Thread with Replies
Related Forum Messages:
Subtotal Total Same Query
trying to run this query php program, is it possible or practical? query works if i take out line "sum(subtotal) as calcTotal" otherwise it errors out. any other way i can calculate total of all the items? $sql = $connection->query("select sum((itemProdPrice * itemProdSize)*itemProdQty ) as subtotal, sum(subtotal) as calcTotal from items where itemOrdId = {$_SESSION['order_no']} and itemCustId = '$custId' ");
View Replies !
Total Like Records Query
Ime probably missing the boat here so please be patient. I am trying to create a query to display the total number of records with the same parent name. Eg Total records for author Eddings = 10 So in effect i want to count all the entries and display only the numeric figure of books for that author. I think its supposed to look something like: Select sum(Eddings) $total From books
View Replies !
Query Pages Printed By Percentage Of Total?
I'm very new to MySQL and have a query I'm trying to achieve, if anyone can point me in the right direction, I would greatly appreciate it. I have a table created to store print job logs from a print server: Table = printLogs logNum mediumint(8) logDateTime datetime docNum tinyint(3) docName varchar(200) owner varchar(25) printerName varchar(25) printerPort varchar(50) sizeInBytes varchar(50) pagesPrinted smallint(10) What I'm trying to do is find a query that, based on a 'printerName', will display 'owner' and percentage derived from the total number of pages printed to that 'printerName' for a given date range. Something like this: +--------+--------------------------+ | owner | Percentage of total jobs | +--------+--------------------------+ | Mary | 50% | | John | 30% | | Kim | 10% | | Sue | 10% | +--------+--------------------------+ Each row of the table is a different print job and can be one of about 20 different owners who have printed any number of pages to any of 10 printers. The main reason for the query is to be to determine which group/owner uses each printer the most.
View Replies !
Calcuate Row Total And Its Cummulative Total
1. I hava a table structure like. EID Day1 Day2 Day3 RowTotal CumTotal And Results will be EID Day1 Day2 Day3 RowTotal CumTotal 1 1 0 0 1 1 1 0 1 0 1 2 1 0 0 1 1 3 How to calculate RowTotal and CumTotal in INSERT or UPDATE or SELECT Statement 2. I hava a table structure like. EID Day1 Day2 Day3 1 1 0 0 1 0 1 0 1 0 0 1 But I want after SELECT Statements Executes in the Following EID Day1 Day2 Day3 RowTotal CumTotal 1 1 0 0 1 1 1 0 1 0 1 2 1 0 0 1 1 3 How to calculate RowTotal and CumTotal in SELECT Statement
View Replies !
How To Get The Total Number Of Rows With A Query "limit" ?
I would like to paginate the results of a query on several pages. So I use a query with a limit X offset Y to display X results on a page. But for the first page, I need to run the same query with a count(*) to know how many pages I will get (number total of rows/ X). The problem is my query is very slow (maybe 5s) because there is much worch to do, and on the first page, I need to run this query twice (not exactly, but ...) so the page is very very slow to load. My question is : is there a function to get the total number of rows even on a query with "limit" ? Or what could I do else ?
View Replies !
Trying To Get Total Count Plus Count From WHERE Clause In One Query
I am fairly familiar with mysql, have been using it with php for about a year now for my development work. One query I am stuck on is the following. I am doing reporting, and need to find the count from a WHERE clause compared to the total count for that group. For the sake of an example, let's say I have a prize table that contains a complete list of prizes. There is a column to list if the prize has been won or not (prizeUsed - Y or N) and another that has the prize name (prizeName - string). I want the final result to list something like: Total Prizes ____________________ 100 of 850 ......
View Replies !
Add A Column To Get A Total
I need to know how to add a column of numbers and get a total using my sql command. SELECT v.vid, title, purchdate, sum(cost) AS Total FROM Video v, Vidcopy vc WHERE vid.v = vid.vc I need to know how to add the cost column and get a total.
View Replies !
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
View Replies !
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.
View Replies !
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.
View Replies !
Running Total
I came across a question earlier on, where i thought they wanted to order by a count in the query and keep a running total. Which it turned out they didn't.. However, that left me wondering.. Is it possible in MySQL? I'm using MySQL 4.1.22. I have set up a basic test table ( this is just a play, this isn't a serious project, i'm just wondering!) which has the following data / layout on creation: Code: ....
View Replies !
TOTAL For Each Agent
I have wrote an SQL to get some information from a database that lists all agents and how long they have worked on a task in a particular queue. Example AGENT | WORK DONE | QUEUE A | 2 | GROUP 1 A | 3 | GROUP 2 B | 1 | GROUP 1 What I would like to do is write a query that will add a row to total the number of work AGENT A has done e.g. AGENT | WORK DONE | QUEUE A | 2 | GROUP 1 A | 3 | GROUP 2 A | 5 | B | 1 | GROUP 1 Is this possible?
View Replies !
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?
View Replies !
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)){ ... }
View Replies !
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?
View Replies !
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.
View Replies !
Count Total Occurrence
I have a table that contains a number of columns, two of which are: name (varchar, text) type (varchar, text) I can't get my head around how to get the count of the total occurrence of a particular type (where type = 'sometype') of the 'name' who has saved the most of that particular 'type's.
View Replies !
Calculate Total Ip Address
I have a database which is full of: ip address - uploaded - downloaded. What i am trying to do is find the totals for each ip address (repeats of the same ip gives the sum on the uploaded/downloaded) and then order the query by the ratio (total upload/total download). But im having difficulties constructing the query which will get the sums of uploads and downloads from the same ip and also order by the query without getting errors (divide by zero when total download is 0).
View Replies !
Count Total BBS Posts
Hello, I have a small problem. I have two tables running the BBS posts and topics. The structure is as follows: BBS Posts post_id,topic_id,author_id,emoticon_id,post_field,uploaded_pic,post_date BBS Topics topic_id,topic_views,post_id,forum_id,topic_name,topic_author,topic_posted,topic_locked I want to do a total post count for each forum_id, and I can't figure out how to tie those two together.
View Replies !
Total Of A Multidimensional Array
I have a multidimensional array... while($row = mysql_fetch_array($query)) { $array[$row['type']] = $row; } Later on I display the value for type 'session' in a table... <? echo $array['session']['count']; ?> Question... I am now expanding the query that will include more values in the array (i.e. month to date rather than just a particular date)... How can I display the total value for this array (not the count)?
View Replies !
Getting Total Row Count When Using LIMIT
I'm having trouble explaining this to some of my friends at the moment so hopefully I'll be get across what I'm trying to do to someone in here =) Basically I'm running a site at the moment where I display a fair amount of data, enough where I have to display it over multiple pages. To do this at the moment I'm running a query on my information once to get the total number of records, then using this to get total page numbers followed by running the same query again but using LIMIT to get the data for the individual page I'm looking at. What I'm trying to do is speed things up by removing the need for running the two seperate queries. So I'm looking for a way to get the maximum number of results possible, but only return a limited amount of them. Is this possible, or is anyone aware of a work around to achieve the same thing?
View Replies !
Getting Rank Out Of Total For An Entry
Let's say I have a table which tally the most popular people. The table has the following structure: people ------ int id varchar name int votes I then find the rank of a person(id=10) based on how popular he is: SET @rownum := 0; SELECT * FROM ( SELECT @rownum:=@rownum+1 as rank, id FROM people ORDER BY votes DESC ) AS ranked_person WHERE id = 10; On top of that I want to know how he ranks from the total above(rank 3 of 100). The above sql is a simplified version of what I have. The real inner sql above contains a bunch of joins and where clauses across many tables. Using SQL_CALC_FOUND_ROWS in the inner SELECT stmt gives me: Incorrect usage/placement of 'SQL_CALC_FOUND_ROWS' Is there a way to get the total results from the inner select within sql? I can do it through server side code but I want to avoid that method.
View Replies !
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?
View Replies !
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.
View Replies !
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?
View Replies !
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?
View Replies !
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?
View Replies !
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.
View Replies !
How Do I Get The Total Count By Unique
I am interested to get the total count of rows for a selected time period based on certain conditions AND unique values in certain field, in this case the IP field. What is the command for this? FYI, I tried this commands but it is not working: SELECT COUNT(click_id) AS total_clicks FROM track_click WHERE click_date > '2008-1-1' AND source = 'poll' AND UNIQUE(ip);
View Replies !
Get Total Number Of Results But Still Use LIMIT?
Is there a (better) way to get the toal number of results from a query but still limit them with a LIMIT clause. For example, in a Google search result, it displays 1-100 of 10,000,000 results. Do they hit the database once for results, and again for a total count or is it all in one query?
View Replies !
LIMIT :: Total Number Of Records?
If I have a sql statement like this: Code: SELECT id FROM `asset` WHERE field = 'value' LIMIT 20 , 40 Is it possible to know how many results there would've been if I hadn't used the LIMIT? Or will I need to do the above query and just count the results first, then apply the LIMIT and run the query again to get my limited set of results?
View Replies !
Counting Total No. Of Columns In A Table
I am having problem in connection mysql with java. Curretly I am using JConnector. I want to know the query for total no. of columns in my table which will help me in feeding up the data. I have tried following queries ------------------------------------------------------------------- mysql> select count(*) from ALL_TAB_COLUMN where TABLENAME=REFERENCE; ERROR 1146 (42S02): Table 'customer.all_tab_column' doesn't exist mysql> select count(*) from ALL_TAB_COLUMN where TABLENAME=REFERENCE; ERROR 1146 (42S02): Table 'customer.all_tab_column' doesn't exist mysql> SELECT COUNT(*) FROM R_TAB_COLUMNS WHERE TABLENAME=REFERENCE; ERROR 1146 (42S02): Table 'customer.r_tab_columns' doesn't exist mysql> SELECT COUNT(*) FROM user_TAB_COLUMNS WHERE TABLENAME=REFERENCE; ERROR 1146 (42S02): Table 'customer.user_tab_columns' doesn't exist mysql> SELECT COUNT(*) FROM user_TAB_COLUMNS WHERE TABLENAME=REFERENCE; ERROR 1146 (42S02): Table 'customer.user_tab_columns' doesn't exist ------------------------------------------------------------------------
View Replies !
How To: Get Total Numbers Of Tables In A Database
I know how to get names tables from a database, using SHOW TABLES. but I want to get how many tables are in a database. I'm working in C# and I would like to get tables name over a string array variable, so before I get names I need to dimension this variable base on database's tables quantity. Is a way to get this qty from database or do I need to loop show tables to get it?
View Replies !
Database Total Size In Bytes
I am using PHP/MySQL and I am wondering what type of query would I have to do to get the total size of the database in bytes... So this would include all the content. The reason is because I am trying to limit users to uploading files if they are over their max allowance.so I need to run a check.
View Replies !
Can I Get A Total COUNT From A UNION DISTINCT?
I've got a rather large table (18M rows) of customer prospects and I need to count the number of rows that can be returned for different customers. The wrinkle here is that I have over 20 different "scores" for each prospect and they can be combined in virtually any combination which means I cannot index them all in one index as that's too many for one index and the leftmost prefix rule means the indexes would be ignored if one of the scores wasn't used. The fastest query I've been able to construct is using UNION with individual indexes for each score: SELECT COUNT(memberID) FROM members WHERE score1 > 100 AND areaCode IN (1, 2, 3, 4) UNION DISTINCT SELECT COUNT(memberID) FROM members WHERE score2 > 100 AND areaCode IN (1, 2, 3, 4) UNION DISTINCT SELECT COUNT(memberID) FROM members WHERE score7 > 100 AND areaCode IN (1, 2, 3, 4) Now unfortunately this is giving me three counts of memberIDs, one for each SELECT statement, whereas I want a total number of distinct rows for the whole statement. I can't just add the three results together as each count can contain some duplicates which means the total would be inaccurate. If I remove the COUNT() from the queries (i.e. SELECT memberID FROM members) it returns large recordsets of around 50K rows but I get a list of distinct IDs and doing a countrows on this is 100% accurate. But as you can imagine this is slow and I don't actually need this large recordset it returns. Is this slow because of the large numbers or rows or because it's merging the list to be distinct? If it's the latter then getting a distinct COUNT() will be slow anyway. But, assuming it's not the merge that is slowing things can anyone suggest a way of getting an overall count of distinct records?
View Replies !
Total Number Of Rows Affected
in Mysql Control Center, how can I modify the Total Number of Rows Affected in a query?? in fact I want to disply all the records of the table but I get only 1000 rows, is it possible to do that and how?
View Replies !
Is There A Command For Getting Total Values Of A Field?
Is there a MySQL command to select the contents of a field and then add the values together to get the total value? I mean lets say we have a field called: X And that field can contain any value in there, from 1 to whatever. And we want to get the total values that are in that field for the selected range. So if I use SELECT COUNT(news_flag) FROM advert_impressions WHERE DATE > '2008-03-10' AND news_flag > 0; this gives me the total count of rows that i have matching above WHERE condition but it does not give me the total value of the numbers in that field for selected date range. What is the MySQL command for getting such total value?
View Replies !
Get Counter Of Total Number Of Fields Of Table
I am trying to prepare one query, which returns me total number of fields of table.. for example... I have one table in that i have below fields Id fname lname email password date modify_date So here is a 7 fields in table.. But I want to count every table fields. for that how can i write query? I have tried this way.. SELECT COUNT( * ) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '<table_name>' But It is not working properly.. So is there any other way to get counter of table fields?
View Replies !
Auto Calculate Total DAYS For Leave, Help Please!
I want to auto calculate DAYS for leave taken based on date key-in by user: For example: Start date: 15-04-2004 -- (Friday) End date: 18-04-2004 -- (Monday) Days: 3 -- (calculate automatically) From the date given above, system will automatically:- 1) Count days as 3 -- assuming working day is 15, 16 and 18 only. 2) Sunday will not be count as working day. Is this possible with MySQL(3.32) + PHP?
View Replies !
Limit Results And Count Total Rows?
I have my database set up with 3 tables: POSTS, TAGS, and TAGMAP. Here is an example: TABLE "posts" id title content time -- -------- ----------- ---- 12 my blog title 1 bla bla bla... 1218826136 13 my blog title 2 bla bla bla... 1218826136 14 my blog title 3 bla bla bla... 1218826136 TABLE "tags" id name -- ----- 45 cars 46 trucks 47 motorcycles TABLE "tagmap" id tag_id post_id -- ------ ------- 156 46 12 157 46 14 158 45 12 159 47 13
View Replies !
|
TRACKER
