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 Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
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 ------------------------------------------------------------------------
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)){ ... }
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?
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.
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' ");
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)?
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
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?
Running Total Of A Column On A SELECT
Is it possible to add the the running total of a column to the next row, something like this: item,amount ----- x,100 y,200 z,50 and get a result like this: item,amount ----- x,100 y,300 z,350
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?
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?
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?
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.
Finding Column Total In Specified Date Range
I'm stumped on an issue that I'm hoping someone can shed some light on. I have a table that tracks financial transactions for multiple members with two key fields being 'available' and 'pending' balances. I am trying to write a select query that will allow me to view the total available and pending balances for all members in a specified date range. Essentially what I need to do is select the sum of each balance type from each unique member at the latest point in the given date range. I've been toying with queries like: SELECT id, MAX(available_balance) AS available, MAX(pending_balance) AS pending FROM members.trans_hist WHERE date BETWEEN '$fromDate' AND '$toDate' GROUP BY pt_id"
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.
Store Count Total From Another Table Dynamically?
I have one table with a list of members of certain groups, and another table with group info. I want to count the members of the group from group_members and add the total to a "member count" column in my group_info table. Is this possible?
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?
4.1 - Update A Field In Table1 With Total Aggregate From Another Table
I want to update table1.field3 with the SUM() of table2.field4 where table1.id = table2.table1_id So I need to agrregate table2.field4, get the sum where table1.id = table2.table1_id. then take that total and put it into table1.field3 Can you do that in one statement or a series of statements - all using DML?
The Total Number Of Locks Exceeds The Lock Table Size
I have a quite large table (about 70 mln rows, MySQL 5.0.18 on a linux box) and when i try to delete some records from it I started getting this message: Quote:#HY000The total number of locks exceeds the lock table size
Sub Counting For Each Row
For each row in my table, I'm getting stuck doing a count. An extract of the table is as follows: user_id | name | favourite_user 1 | "person 1" | 2 2 | "person 2" | 1 3 | "person 3" | 1 4 | "person 4" | 2 5 | "person 5" | 2 I'm trying to select all rows from the table, and at the same time, a count of how popular each person is. In two queries, this can be achieved by: SELECT * FROM TABLE; SELECT favourite_user, COUNT(*) FROM TABLE GROUP BY favourite_user How can I combine the queries so that for each person I return their data and a count of their popularity?
Counting Records
Got a question for you that is really stumping me. I have 3 tables job_jobs ------------ job_id job_name . . . job_cats ------------- cat_id cat_name . . . job_jobsToCats ------------- jtc_id job_id cat_id *************** ok i have those 3 tables to categorise the jobs on the site and im trying to build a query that will essentially get all categry names and display the number of jobs in that category so when i output it i will get accounting (12) admin(2) etc etc
Subquery And Counting
I have a table called people in my database. it looks like this +----+------+ | id | name | +----+------+ | 2 | bob | | 1 | Jill | | 3 | Jill | | 4 | John | | 5 | Jill | +----+------+ I want to display the distinct name and how many times they are in the table So like this Jill - 3 bob - 1 John - 1 How would I go about doing this. I thought maybe something like this but not to sure [code] SELECT COUNT(name) FROM people WHERE name = (SELECT DISTINCT name FROM people); [code]
Counting Like A Human
I have a query that does an ORDER BY area ASC. When it does the order by it is ordering by how a computer counts rather than how people count. As an example here is some of my data ....
Counting Query
Is there a MySQL function that i can use to just count the number of rows in a table instead of listing all the IDs and running a command on the result?
Counting Rows
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home2/itsyouma/public_html/staff/memberlist.php on line 6 <?php $SQL_statement = "SELECT * FROM users"; $resultset = mysql_query($SQL_statement); $count_rows = mysql_num_rows($resultset); ?>
Counting Rows
I have a table containing Article | State ------------------- 1 | 10 2 | 10 1 | 5 1 | 5 and I want to count all Articles in one row with the different states SELECT Article, COUNT(State=10) AS Ordered, COUNT(State=5) AS Free FROM...... GROUP.... but this SQL doesn't work (on my Server 4.0.20) I don't know if it work above but in the docu I can read somthing COUNT(expr) my be it works in 4.1 but is there another way that works wit 4.0.20?
Hour Counting
I keep in a ltable 'log' the time and date each user loged-in, and the time and tahe each user loged-out... among other things. What I want to do is a report to show how long each user was loged in the system. I'm using MySQL 4.0.15. I was thinking if I could do it with a single query but I cant figure out how. Table log: ....
Counting Query
I need a slect statement to return the count of rof rows if a particular field matches a criteria similar to the COUNTIF function in Excel. We are using mySQL 3.23.58. Is there a way to do this and if so how?
Counting Days
How can I make a query such that in the "WHERE" part of my clause, I want to put something to the effect that, say, X days have elapsed since a given date. For example, suppose in my table, I have a boolean field and a date field. I want to create a query asking for those rows where X days have elapsed from the date field in a row and the boolean, say, is false. Do I need to create a third field, called, say "X" for how many days have elapsed between the date field and today, and update every row in the table every day?
|
TRACKER
