MYSQL :: Total Like Records Query

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' ");

Query Total Execution Time
How to get total time taken by a query to execute....

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.

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,
ok.

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?

Deleting Records Upon Query... (!?!)
Sorry for the confusing thread title. Here's what I am trying to do...

Read the last six added records for a particular criteria, and delete the rest. Now consider I have around 1000 unique "criteria" and this query is executed a few times a day for each unique... should I delete it on each read or put it in a cron job and schedule it for every night.

Here's a sample MySQL syntax for my selectquery:

SELECT * FROM table1
WHERE field1 = sometext
ORDER BY some_time_field ASC
LIMIT 0,6
So the questions, again, are:Can I join this SELECT query with a DELETE, and delete all field1s with "sometext" which were not selected by the query?If I can't, how would I do it with a seperate DELETE query? i.e. Leave the last six records for field1 = sometext and delete the rest?Do I really need to order by some_time_field? Or does MySQL have an internal schema to sort records by their "internal" creation date?

Query Help Looping Through Records
The below Query loops through two tables in mysql and outputs all records where a match_date in a reports table (re) is equal to a match_date in a fixtures table (f).

Now this works well, but what I want to do is say to my query is find all of these dates BUT

- As soon as the FIRST f.date is found that does not have a re.date matching it output that information and limit it to 1 .....

Count Records Within Query
I have the following information in table 'Test1':

field1  field 2
000001  000000
000002  000001
000003  000000
000004  000000

I want to write a query that will only display the unique field 2 records and as well provide a count of how many records have that id.

output should look like

field2  recno
000000    3
000001    1

can some one help with the trick to make this work?

Query Cannot Retrieves All The Records
I've got this problem, this query is used to create a combo wich display all months where at least a news is been submited (a kind of hostory menÃ¹), the fact is that it can display max the months wich correspond to the 107 record submited.

The query:

select date_format(data,"%b/%Y")as textMonth, month(data)as month,dayofmonth(data)as day,year(data)as year from archivio where category = "Rassegna Stampa" group by month

Just to be shure I've also tried the query above on mysql browser, and the result is the same. Can you tell me if there's a kind of limit?

Query And Non Existent Records
I have a mysql table texts which keeps textlines in different languages.

columns: textId, languageCode, textline

The application adds records for specific languages as:

textId 1
languageCode: EN
textline: "Username"

another record could be:
textId 1
languageCode: DE
textline "Anwendername"

Now at a certain pojnt in the app I have to check whether for a specific textId there are textline filled in for specific languageCode. For example: Is there a textline for records with textIds 120, 124 and 134. And are these records present for EN and FR (French). I can query as:

Select count(*) from texts where textline != '' and textId in (120,124,134);

But what when the records are there in ENglish and German but not in French?

Any ideas how to solve this?

Query To Get Records In One Table But Not In Another One.
I have this query that works but now I want to refine it so that it only selects records in the loaninfo table that do not have a record in the achtransfer table. I not sure how to do that. Here is the query I have now....

MySQL Query For All Records With NULL Value
I have recently been trying to extract a few products from a database (that is quite big), so that I can find products that have a specific NULL Value, what kind of a SQL statement would allow me to do this?

Update A Set Of Records In One Single Query
My records contain a position, from 1 to n. This position is not the primary key.
In normal conditions, the position of this records should be as follows:
1, 2, 3, 4, 5, 6, 7, 8, 9 . etc...

One of my utitities is meant to check the consistency of the table -
one of the things it has to do is scan the table as follows:

SELECT position FROM employe_records WHERE employe_id = ? ORDER BY position

From the result of this query I verify each entry (JDBC) one by one, looking for inconsistancies. In a for() loop I look at the value of position and check that they start from 1 and are incremented by one for each record.

At the first inconsistency I come accross I want to run a query to update the records so it is consistant again - it is something like this:

UPDATE employe_records SET position = updatedValue FROM
employe_records WHERE employe_id = ? ORDER BY position

This is not valid query of course - my question is what should my query look like?! How can I make 'updatedValue' incremented by one automatically so each of the records are updated from 1 to n.

How Can I Update A Set Of Records In One Single Query
My records contain a position, from 1 to n. This position is not the primary key.
In normal conditions, the position of this records should be as follows:

1, 2, 3, 4, 5, 6, 7, 8, 9 .etc...

One of my utitities is meant to check the consistency of the table - one of the things it has to do is scan the table as follows:

SELECT position FROM employe_records WHERE employe_id = ? ORDER BY position

From the result of this query I verify each entry (JDBC) one by one, looking for inconsistancies. In a for() loop I look at the value of position and check that they start from 1 and are incremented by one for each record.

At the first inconsistency I come accross I want to run a query to update the records so it is consistant again - it is something like this:

UPDATE employe_records SET position = updatedValue FROM
employe_records WHERE employe_id = ? ORDER BY position

This is not valid query of course - my question is what should my query look like?! How can I make 'updatedValue' incremented by one automatically so each of the records are updated from 1 to n.

Query For All Records With Datetime In 2005?
I have a table with a datetime field. I'd like to query all records for 2005 based on that field. I'm having trouble coming up with a where clause that does this.

I'm on MySQL 4.1

Select Last N Records Matching A Query.
I am trying to select the last n records in a database which conform to criteria selected by a user. I gather that there is no "BOTTOM" equivalent to the "SELECT TOP" command. I've tried "SELECT... ORDER BY... LIMIT X,Y" but MySQL doesn't seem to take a second parameter for the LIMIT statement. Is there any way round this?

List The Number Of Records In Query
I have a very simple query. I want to list a bunch of names & how old they are. (two columns) I want to add a third column that basically counts how many records there are.

Example:

1. Bill Jones - 55
2. Bob Smith - 56
3. Steve Gates - 25

Can I do this in mysql or should I add the code in PHP, and how do I do it?

Query To Display Records Over A Certain Number?
think my brain is now officially fried as i can not come up with this query....

i want to display records from my table where the record count is say over 5.

real simple...two columns a and b. 10 records in the table....

IN Query Showing All Records If Used With Same Field Twice
I have 2 tables programs and programType. programs has 5 records and programType has no records. When I run a query like "SELECT * FROM programs where id in (select programid from programType) and id in (select programid from programType)" I get all records But When I try to run "SELECT * FROM programs where id in (select programid from programType)" Then I get no records.

Counting Records In 2 Tables Using 1 Query
I have these 2 queries.

SELECT count(*) gifts
FROM gift g
WHERE g.this and g.that

SELECT count(*) events
FROM events e
WHERE e.this and e.the other thing

is there a way to put these into one query.....

SELECT count(g.*) gifts, count(e.*)
FROM gift g, event e
WHERE . . . .

so far nothing seems to be working .....

Flag Records Not To Be Reused After Query
I'm a MySQL novice, and I don't even know if there is a way to do this.

I have a simple table containing just 2 fields and about 15,000 entries...one contains an ID (which is just an incremental number) and the other a phrase.

I will be doing queries selecting 50 random entries from the table. Somehow, once those 50 are selected and returned, I would like to flag them so that they cannot be selected again UNTIL I exhaust the entire list. So, I'd like to get 50 random at a time, until all 15,000 are gone, and then start over.

If that isn't possible, I suppose I can just select the first 50 every time and delete the first 50 when I am done.

Duplicate Records Delete Query
I have a table with duplicate records but the key of the duplicate records is different, that is:

The key of one of the record has the format:

Rssss

The key of the second record has the format:

ssss

How can I delete both records from the table?

Query To Get Matched And Mismatched Records
this is my first time on the forums and i need help with my dbase. i need to match fullnames from Table1 with the fullnames in a QryCustomer. Table1 contains only the fullname(one field) whereas QryCustomer contains a list of fullname with their respective Account# and Customer#..i need to match the fullnames and a separate query to get the mismatched names.

Update Query On 14000 Records
Haing moved a large site from asp and access to php and mysql, I would like to change the way of my fields is delimited.
Currently, the field contains user selection seperated by commas
e.g. selection1, selection2, selection3
I would like to update this field on every row in the table and change all commas to pipes
e.g. selection1|selection2|selection3
The table contains over 14000 records, and the field in question can contain anything up to 30 selections.
Is this possible to do?

Sql Query For Count Records Of Current Week
I have a registration form that saves the registration date on a datetime column. I would like make a query to know how many records are in the table that where for the current week. For example, I'm on week 25 and I want to know how many records I have for this week without the need of specifying the from and to dates. Is there a way to do this?

How To Specific Number Of Records To Retreive In A Query?
My item file contains too many records to display. I want to limit the display to only 20 records at a time.

In the Select statement, how do I specify the the first 20 records, 2nd 20, etc.?

Selecting Records Younger Than 24 Hours Using SQL Query?
I have a TIMESTAMP(14)-field in my db and I need a quick way to select records younger than 24 hours, WITHOUT using PHP to convert around. I'm pretty sure this one should be quite easy, played around with something like:

SELECT fields FROM db WHERE 'timestamp'>NOW()-INTERVAL 1 DAY)

for hours right now (with DATE, EXTRACT, BETWEEN..AND etc.), and I either got error in my queries or bogus results.

How To Show All Records In MYSQL Query Using Default Value
I'm working on somesite and i face this problem:

i want to send a parameter entered by a user from text box that called (txt1) to the query below ....

Query By Dates To Find History Records
what is the correct way to query on a date field in a table to pull a value that is older than 3 days?

Date field format is 2007-04-04

I only want records where the date is 2007-04-01 or earlier.

Buliding A Query:selecting Unique Records
I am nearly positive that there is some way to have mysql select only the first occurance of a data entity on a select.

Maybe a better explanation is given by example. Think of having a table of employees, with one of the columns being their bosses email address. Now I want to do a select statement on the bosses email addresses that only returns one entry for each bosses email, even though that bosses email address will occour > 1 in the table. (more than 1 employee has the same boss...)

I could parse out the duplicates when I get to PHP but I would rather have mysql do the work for me.

Query To Result Out The Newly Inserted Records To Txt File
Can any one help me with a query to result out the newly inserted records from a MYSQL table to a text file on a hourly basis.

Using A Query To Exclude Records From One Table, If The Field Value Exists In Another
I'm very new to SQL, and wondered if someone could help me out:

I am building a query which contains two tables. One table has details of everything everyone owns.

The other table is a list of things which people own, but that I don't want to see.

Is it possible to design the query so that I see all of what everyone owns, except the items listed in the other table?

Using Limit On Grouped Query With Large Number Or Records
I have a query that use a "group by" clause that returns 600+ queries

however if I add a LIMIT 500,50

It returns 0 records. I tested LIMIT 450,50 and it returned only 34 records.

Is there something limiting the limit? Is there an option in mysql that can increase this limit?

1 Line Query To Delete Specific Records From Multiple Tables
On clients machine, currently to delete on trainee record it runs 10 queries to delete records from 10 tables. At the time of running all queries, server shows (104) Connection reset by peer. An error condition occurred while reading data from the network.

I think it because of running 10 queries at a same time. Is there any possibility that through one line of query we can delete record from 10 tables.

I've tried following query

DELETE FROM table1, table2, table3, table4, table5, table6, table7, table8, table9, table10 WHERE empID = 11;

But it gives ' error in query.

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.

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.

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