MYSQL :: Where Clause Issues When Selecting Between Dates With Aggregate Functions

Aggregate Functions
I have a table PUPILS with a field Name (varchar). The problem: how to select a longest Name in the table and its length? I mean something like this:

SELECT Name, (MAX(LENGTH(Name))...

It doesn't work, of course. I tried 'grouping by' but this also fails.

Aggregate Functions
I have defined a table like this:

Wrote(Aname varchar(50), ISBN varchar(30))

In oracle I could run the following query:

select max(count(*)) from Wrote group by ISBN;

However, in mysql it says invalid use of group function when I try that.
What is the myql equivalent?
I've been using this:

select max(cnt) from(select count(*) cnt from Wrote group by ISBN) A;
Is that the simplest way to do it in mysql?

Aggregate Functions
think i should get a bed here atm, you guys are such a help.

i dont think this is possible but would be GRAND if it was... i want to be able to do a calcuation on two SUM's in an sql statement.

Quote: SELECT gref, pref,COUNT(pref) as played, SUM(amountwon) as won, SUM(stake)as staked, players.*
FROM winnings, players
WHERE pref = playerref
group by pref
order by playername

i would like to work out a balance.. WON - STAKED. but i cant get it to work.. well i can with php after but this means i cant sort on it which would be what i want to produce a rankings table with the leaders sorted by the highest balance.

Aggregate Functions + ORDER BY
I want to find the sum of some rows, which I can do fine. Then, I want to order by that sum. Is this possible? I know ms access does it the following way, which I've read will work with mysql also:

SELECT nav_result_id, SUM(timingpoints) AS TimingPoints
FROM leg_results
GROUP BY nav_result_id
ORDER BY SUM(timingpoints)

However, I get this error message: "#HY000Invalid Use of Group Function"

Can anyone tell me why, and possibly give me a hint how to correct my syntax to prevent this, and make it work

Adding Aggregate Functions With Columns
Does anyone know how to add the values returned from aggregate functions to the values in the columns.
I have to compute the average price of a product given from a list of products which have their own prices, and
display the products information with its price, the average price including that product, and the average price not including that product.
so far it got this :----:
select
(select avg(pprice) from vendor) , (select max(pprice) from vendor),
(select min(pprice) from vendor), avg(pprice) ,
vname
from vendor a
group by vname;

which calculates the average but then i got lost.....

Show Rows With Result 0 For Aggregate Functions
have a query like this:
SELECT C.idc, C.name, count(CM.idm)
from C, CM
where C.IDC=CM.IDC and CM.idci is null and C.type='class' GROUP BY C.IDC

The result table only contains the elements of table C where the count() is >0.

How can I obtain a result table that contains all elements of table C with their count (either 0 or >0) ?

Functions For Inserting Dates Into Mysql
Just wondering, besides now(), what are some other functions in mysql for inserting today's date into a database? For example, one of the field's I have is of type "date" that will hold today's date (but not time).

Selecting Dates
I have a table with events, which have a EVENT_DATE column of type date. For events listings I'm using the following SQL query to retrieve the events of the next 7 days:
PHP Code:

"SELECT event_id FROM events WHERE DATE_ADD(CURDATE(),INTERVAL 7 DAY) >= event_date ORDER BY event_date ASC"

It does return the events of the next 7 days, but it also returns an event that happened 2 days ago. Any idea why this is happening?

I thought the above SQL query means, select all events that are today (CURDATE()) or within the next 7 days

Selecting A Series Of Dates
I have a bit of a head-scratcher. I'm working on an art gallery site and need to display past, present, upcoming (on deck) and future shows (anything after the upcoming show). The show table has start_date and end_date DATETIME columns.

Getting the past shows doesn't seem to be too difficult;
SELECT end_date FROM table WHERE end_date < now()

However, getting the current show, upcoming show and then future shows has smoke coming from my ears. I can write it in English, but am not sure what to do to retrieve the data.

// current
select start_date from table where month and year = 'current month and year'

// upcoming
select start_date from table where start_date > 'current end date' LIMIT 1

// future
select start_date from table where start_date > 'upcoming end date'

I've been looking through the mysql date and time functions, but am having a difficult time trying to wrap my head around it. Can this even be done in mysql? Or do I need to use PHP to determine the dates and then query the db?

Selecting Between Dates With Different Timestamps
I'm in a bit of a mess here, trying to deal with some timestamp
issues.

I need to select items from a database with a datetime timestamp:

0000-00-00 00:00:00

I need to select these items based on several different criteria, for
which I will use separate select statements, but some of them are a
bit confusing.

I need to select items that are less than 24 hours old, between 24 and
48 hours old, between 3 to 5 days olf, between 6 to 10 days old,
between 11 to 30 days old, and items older than 30 days.

In the past when comparing times I have always converted the database
timestamp to Unix time, set a nowTime variable to current time in unix
time, and done the addition and subtraction. In this case, that
doesn't necessarily work, as I need to use "BETWEEN" in the
timestamps, and I'd really rather handle the separation of times with
the select statement than in the code.

Can anyone point me in the right direction, I'm not sure what the
select statement should look like.

Selecting Data Between Two Dates
I'm trying to return data for each day in a date range. If the day does not have a total I would like to use the last previous rows total for a new row. I have no idea how to do this. This sql returns.

select
ag.agcy_name, date(a.acct_signup_date), count(m.mem_member_id)
from transactions t
left join txn_type txt on t.txn_type_id = txt.txn_type_id
left join travel_type trt on trt.travel_type_id = t.travel_type_id
left join segment s on t.segment_id = s.segment_id
left join member m on m.mem_member_id = t.member_id
left join account a on a.acct_account_id = m.mem_account_id
left join agency ag on ag.agcy_agency_id = a.acct_agency_id
left join group_member_relation g on g.group_mem_rel_member_id = t.member_id
where a.acct_status_id ='1'
and a.acct_signup_date between ? and ?
group by ag.agcy_name, date(a.acct_signup_date) .

Selecting Dates For A Schedule
I have a list of jobs scheduled in a MySQL table, with start dates and
end dates, like so:

SchedID | JobID | StartDate | EndDate |
----------------------------------------------
1 5 2006-05-08 2006-05-09
2 8 2006-05-10 2006-05-12
3 3 2006-05-01 2006-05-19
4 9 2006-05-09 2006-05-11
5 6 2006-05-14 2006-05-19

In my web application, I have a request to show scheduled jobs between
certain dates, usually in 1-week or 2-week views.

I thought this was super easy, but I've run into a problem:
How do I show jobs scheduled for the week of 2006-05-07 to 2006-05-13?

Originally, my query was something along the line of

"SELECT * FROM SchedJobs WHERE EndDate < 2006-05-13"
....or...
"SELECT * FROM SchedJobs WHERE StartDate BETWEEN '2006-05-07' AND '2006-
05-13'"

.... but of course, both queries will fail to include the job with SchedID
3 in the result, that starts on 2006-05-01 and ends on 2006-05-19, as it
begins before the requested start date, and ends after the requested end
date.

How can I get a list of job results for everything taking place within
two selected dates?

Selecting Values Between Two Dates
I have the following part of a where statement that is giving me an error:

u.lastactivity between ( now() and date_sub( now(), interval 5 day))

What I am trying to do is select records that are between now() and 2, 5, 10 days earlier than now (that is what the 5 in the statement is). Is this the correct way od determing the date and where is the error in the syntax?

Selecting Dates Ragardless Of Year
I have a table that holds dates in it for an availability calendar script. At the moment I hold the season dates in a date field and colour code the calendar to show the rental season. this is fine for the current year but if I want to display the next year (which has exactly the same rental seasons), I don't get anything coloured because the script is comparing the years along with the rest of teh date.

What I want to be able to do is for example if a rental period is from the 1st of July to the 31st of August to be able to pull out the dates from the database where just those months are included. I store both a start date and an endate for each season in the same record.

I suppose what I am really after is a way to structure a select statement something like this.

select * from pricing where day and month of startdate <= day and month of currentdate and day and month of day and month of endate >= currentdate

Selecting Totals For Multiple Dates
I have a form where a user can input two dates and I want to get a sum of the day's data for each of the days separately.

So far the closest I've come is:

// to display the total for one day
SELECT sum( hplmnmoc )
FROM `inRtccCallType`
WHERE host='wilsle03'
AND date='2007-05-25'

OR

// to display the total for all days
SELECT sum( hplmnmoc )
FROM `inRtccCallType`
WHERE host='wilsle03'
AND date BETWEEN '2007-05-24' AND '2007-05-31'

With what I have so far I can either display one day's total or else a total for the whole period. Can anyone tell me how to get the totals for each day individually without having to perform multiple queries.

Selecting Certain Dates From Date Column
I wonder how this can be done, data is like this

+---------------------+
| whenstamp |
+---------------------+
| 2005-02-21 12:27:54 |
| 2005-02-21 12:27:54 |
| 2005-02-21 12:27:55 |
| 2005-02-21 12:42:55 |
+---------------------+
4 rows in set (0.00 sec)

mysql>

I tried something like this ::
$this_month = mysqli_query($dbcon,"select date_format(whenstamp,'%d-%m-%Y') as datex from logs
where %m = '02'");

Basically I want to retrive records corresponding to the current month, I know I have hardcore '02' in their but that was just for testing and getting started.

Reference To Table Alias In From Clause To Be Used By Subquery InSelect Clause
I am using 5.0.26-NT on Windows 2000.

I have need to use a reference in the outer from clause in a subquery in
the select clause. Consider the following example:

Select (select b.baitID from b where b.entrydate curdate()) as
wantedBaitIDs from bait_tbl b;

My actual need is more complex than this as part of it is a rough cross
tab. If I try to define the table in the alias, not only do I lose
whatever benefits there are in the particular join I would use in the
outer from clause but would also require the join to be defined in each
subquery, requiring it to be examined each time it is used.

To be absolutely clear, in this example I want to use bait_tbl with the
alias of b in the subquery. In my actual query I reference the same
table twice with a different join set for each. I need to reference a
particular alias as that has the join set I need.

Converting MS Access Dates To MySQL Dates During LOAD DATA INPUT ?
My insert code is below. The MySQL server is on my local machine and is version 4.1.22. I am exporting from an access table with 6 fields to a mysql table with those 6 plus 5 more fields. The main problem I am having SO FAR, is converting dates. The data file dates are formated like:
2/2/2006 0:00:00, 12/20/2006 9:22:05

Any ideas how to format those into mysql friendly dates?

Also, how do I convert currency fields in the load data process? I know you use SET by what kind of formula?

===========================================

LOAD DATA INFILE 'C:Documents and Settingspath_to_filedata.txt'
INTO TABLE auto
(field2, field3, field4, field5, currency_field6, field7, date_field8, date_field9)
SET id = MD5(UUID())
FIELDS TERMINATED BY ','
LINES TERMINATED BY '
'

Aggregate SQL
I have a table as follows, representing a series of games for a sport.

+--------+---------+---------+--------+
| gameID | team1id | team2id | winner |
+--------+---------+---------+--------+
| 2502 | 22 | 12 | 12 |
| 2503 | 21 | 13 | 21 |
| 2515 | 13 | 11 | 11 |
| 2516 | 22 | 14 | 14 |

Each game stores the game's winner as the winning team's id in the winner column.
I'd like to produce a table with each team's id, followed by the number of wins and losses, like so:

teamid wins losses
----------------------
1234 | 3 | 2
4321 | 1 | 0

Aggregate AVG
I have a MySQL call:PHP Code:

$sql = mysql_query("SELECT COUNT(id) AS count, AVG(rating_avg) AS avg, SUM(views) AS views FROM articles WHERE author= '".$this->author."' AND status = '10' GROUP BY author");

As you can see I have three aggregate functions in the statement. The problem comes in with getting the average rating -- AVG(rating_avg)The call is looking at several articles written by authors. If an article has not been rated by any visitors, it has a rating of 0.00.Is it possible to setup a mysql statement that gets all three aggregate functions but leaves out a rating of 0 in the average function?

Aggregate Keys
CREATE TABLE `tbl_vel_product` (
`family_id` varchar(25) NOT NULL default '',
`catagory_id` varchar(25) NOT NULL default '',
`colour_id` varchar(25) NOT NULL default '',
`price` int(11) NOT NULL,
`qty_in_stock` int(25) NOT NULL,
PRIMARY KEY (`family_id`,`catagory_id`,`colour_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Where my primary key is several foreign keys from other tables. I have a webform that returns this product key and I wish to search for the product with that key..

How do I construct a query of the form..

SELECT * from tbl products WHERE [family_id, catagory_id, colour_id] = [URL_PARAMETER]...?

Aggregate Function
I am kind of confuse how to select this data Which city has the most airports. There are two tables airport and cities the airport has two column IATA_CODE and the name of airports where as city has the IATA_CODE and the name of the cities.
I used two diffrent ways but I dont't which one is right.

select name, count(distinct IATA_CODE) from airports group by name;

SELECT c.name,count(*) AS no_of_airports
FROM airports a
LEFT JOIN cities c ON c.IATA_CODE =
a.CTY_IATA_CODE
GROUP BY a.CTY_IATA_CODE;
both have the same result and I coudn't be able to select once city who has the most airports.

Aggregate Time Sum
I have many "track length" fields in a table, I wish to have these all added up and produced as a field... Much like "SELECT SUM(`x`) FROM `table` WHERE 1"

I see there is an ADDTIME but... This adds one value to another, I just wish to add all the fields together

Aggregate Function (AVG) Query Q.
I've been looking at the AVG function, and am trying to figure out how to return a very specific, complex value using the function.

Query:
sqlTESTavg_curr = "SELECT AVG(DISTINCT TESTcomp) FROM TEST2005 WHERE (StatusCluster = 'Freshman') AND ((Class_Num = 2)"

I am trying to et the average of current freshmen applicants TEST scores. The problem is, my table is designed to capture a record for every single student every single day. I can specify to look for only records with dates of "today" - but if I try to use the sql above- it's not going to be correct because I have multiple records with identical scores which will skew the average.

We have student IDs that are the table- what I would like to do is set the query to return distinct by the Student ID's but actually return the average of the TESTcomp fields.

so - something like this:
sqlTESTavg_curr = "SELECT DISTINCT STU_ID AVG(TESTcomp) FROM TEST2005 WHERE (StatusCluster = 'Freshman') AND ((Class_Num = 2)"

Obviously that doesn't work. Is this just too complex of a query? How should I go about getting the average of a field on the values from a distinct field that is not the same?

Create Aggregate Function
i want to create my own aggregate function........

when i run the following code :

[code]DELIMITER $$

DROP FUNCTION IF EXISTS `tradedb`.`abc` $$
CREATE AGGREGATE FUNCTION `tradedb`.`abc` () RETURNS DECIMAL
BEGIN

END $$

DELIMITER ;
[code]

i get the following error in the mysql query browser.

Script line: 4AGGREGATE is not supported for stored functions

Any Way To Speed Up Queries That Aggregate?
I am developing a web-based data analysis tool that will (hopefully) include large detailed record databases. I want to make the query response times as fast as possible, though I am not sure how fast that should be. Ultimately, everything will be done through PHP, though I am now testing straight from MySQL.

My first data set includes about 250,000 unique records, with about 120 fields (mostly shortint). Most of those fields are category type fields, that can be used in a GROUP BY statement. I have a few true numerical fields on which mathematical operations (mostly average) will be applied. The problem is that I want the users to be able to select any possible combination of Grouping variables, so there is no way I can index every possible combination. As a result, the queries are somewhat slow when I aggregate using GROUP BY with two categories - around 6 seconds. (Maybe this isn't slow?)

I've tried breaking up the table into smaller tables and joining, but that only makes things worse. The question is - is there anything I can do to set up the database, make sure I'm doing in the query, or change the MySQL settings to take advantage of my hardware? (Our Linux system has 7GB of RAM).

Aggregate/sum With Distinct Conditional
consider the following table:

col1, col2, col3
1, 1.25, 1
1, 1.25, 2
1, 1.25, 2
2, 0.75, 1
2, 0.75, 1
2, 0.75, 1
3, 1.25, 1
3, 1.25, 1
3, 1.25, 1
3, 1.25, 2

from this "theoretical" SQL statement

SELECT col3, IF(DISTINCT(col1),SUM(col2),SUM(0)) As mySum FROM table1 GROUP BY col3

or perhaps this one (neither actually works)

SELECT col3, SUM(IF(DISTINCT(col1), col2, 0)) As mySum FROM table1 GROUP BY col3

result would be...

col3, mySum
1, 3.25
2, 2.50

Mysql 5.0 & ASP/ASP.Net Aggregate SUM Problem
I'm running into a brick wall when attempting to run querys in asp/asp.net with sum() being used.

The issue I'm having is any query containing sum() will no display and breaks the asp code in either asp/asp.net(vb) I am using 3.51 odbc driver dsn-less I have even attempted to cast the sum into a decimal still encountering the issue. Anyone have any ideas as to what 5.0 changed because previously I have no issues with Sum() in my asp/asp.net sql queries.

How To List All Missing Dates Between 2 Dates
I have a database which stored financial data daily exclude weekend, however sometimes i tend to forget to upload data into the database. How do i write a sql query that would detect the missing dates between from last updated date until the current date.

Stroring Dates Perior To 1 AD, BC Dates
I need to store some dates that can be BC, However I received a problem indicating Incorrect Datetime.

I have read the documentation, and it says that Date&DateTime types can store from 1000-9999 AD, and Timestamp stores less year ranges. What other options do I have to store dates before 1000 AD !

List All Dates Between 2 Diff Dates
I have a database which stored financial data daily exclude weekend, however sometimes i tend to forget to upload data into the database. How do i write a sql query that would detect the missing dates between from last updated date until the current date.

Dates, Dates, Dates - Syntax Help Please!
OK, I have a MySQL table which contains, among others, a field called date which stores dates in the format YYYY-MM-DD HH:MM:SS.

What I'd like to do is select a list of all the months present (without repetition), preferably in the format YYYY-mm

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?

Log In Issues
I just started my MySql account from my Yahoo Small Business Webhosting account. I'm confused now, becuase I installed the PhpAdmin tool that they told me to, but now I can't sign into it. The PhpAdmin site says this is a MySql issue. It appears that my username and password aren't correct.

I am positive that I'm giving the username and password that I created when I created my MySql database a few minutes ago. I keep getting this error message: #1045 - Access denied for user 'root'@'localhost' (using password: YES). I tried resetting my password (to the same password that I gave when I started the account and also tried setting it to an entirely new one). The Yahoo help files said: "If your PHP or Perl configuration files require a host name, you may have a problem with your host name. Make sure that you are using the host name mysql, not the default host name localhost." I'm confused as to what username I should be typing, in this case.

Issues With IF()
Has anyone noticed issues with IF() or IF(expr1, CONCAT(exp1, exp2), CONCAT(exp1, exp2)) after upgrading from MySQL 4.0 to 4.1? A query in our system seems to be hanging and eating up some serious CPU usage (around 99%), yet on 4.0, the system seems to breeze through this query.

SQL Query Issues
**********QUESTION CHANGED*****************
Error

SQL query: Documentation

SELECT sum( subtotal ) AS total_count
FROM (

SELECT 'male' AS source, id, thumbnail, name, height, chest, waist, collar, NULL , shoe, eyes, race, hair
FROM male_models
WHERE concat( id, thumbnail, name, height, chest, waist, collar, shoe, eyes, race, hair ) LIKE '%a%'
UNION ALL SELECT 'female' AS source, id, thumbnail, name, height, bust, waist, dress, hips, shoe, eyes, race, hair
FROM female_models
WHERE concat( id, thumbnail, name, height, bust, waist, dress, hips, shoe, eyes, race, hair ) LIKE '%a%'
UNION ALL SELECT 'talent' AS source, id, thumbnail, name, NULL , NULL , NULL , NULL , NULL , NULL , NULL , race, NULL
FROM character_models
WHERE concat( id, thumbnail, name, race ) LIKE '%a%'
)

MySQL said: Documentation
#1248 - Every derived table must have its own alias

Match Against Issues
I have a query that is searching a fulltext field in the database using match against. I get 0 returns, however i believe this might be caused by the keyword being in to many rows and is being ignored by default in mysql. I've posted the query in case i am wrong. However if i'm not wrong how would i create a fulltext search? ....

Access Issues
I just got mySQL today on my fasthosts hosting account.

I want to use the administrator and migration tools which seem very good. I can connect fine to the Fasthosts server, but when I try to view users or add a table I get 'access denied for user xx'@'%' to database 'mysql' 1044.

I was only given one username/pass by my hosting company which I assumed would be the root?

p.s. the migration tools seems great. I managed to migrate my access db to my local machine in 1min!

4.1.14 Upgrade Issues
I had 4.0 running on my local machine but my ISP upgraded to 4.1.14 so I had to as well.

However, the upgrade didn't go as smoothly for me... I removed the service - before I realized I didn't have to. I then installed 4.1.14 without issue But now I can't get the new service to start.

DB Speed Issues?
I am having the oddest issue that I can't put my finger on. It's probably something simple..

I have two DB [prod & stage] on the same RedHat 9 server. These 2 databases share a copy of Apache 1.3.31 with same dbd/dbi drivers. This application is 100% perl with dbd/dbi.

Issue: When I point the code to the staging DB a particular page (Large calendar page, with lots of selects) takes 3 seconds to load. I then point the same code to the prod DB and the page takes 12 seconds to load.

What parameters could cause something this drastic? I will add one more thing here... the volume of data is the same (I copied the prod DB back to staging to rule that possibility out as well.)

Memory Issues
We have recently installed beta version of our application on IBM - Pentium Xeon - dual processor, 1 GB RAM, 80 GB HDD on Fedora Core 3 OS. The kernel version which I currently have is: 2.6.9-1.667 and Mysql Server version: 4.0.20-standard.

I have noticed that mysql processes (using 'top' command) take up substantial amount of memory over period of time. Please take a look at mysql processes output of 'top' command below: Code:

MySQL Issues
I have a few issues related to MySQL. Here are they:

1. As MySQL is acquired by Sun it is not clear how much effort Sun will provide to develop MySQL.

2. The cost might change after the acquisition from Sun which might affect the popularity of MySQL.

3. MySQL might not be suitable for large organisation.

4. Absence of stored procedures, sub-selects and triggers. (Check)

5. Failure to meet the ACID test - it is perfect though for storing/tracking non-critical data. (Check)

Does MySQL 5 corrects these issues? How many of these are sorted in the latest version?

Speed Issues
I have 15,000,000 bank records that are querried against 60,000 census records.

There is a primary index on the census table(concatonation of state/msa and tract) that corresponds to an index on the bank table(not unique on the bank table). I need these indexs to perform timely INNER JOIN querries.

NOTE - all querries that I describe are make temporary tables. They have to be for my app - unless there is an alternate way similar to make tables that I can use. Maybe w/ views?

The proscess works if I filter the bank table down to the state and county levels. If I just filter at the state level the querries drag - Illinois can be 5-10 minutes. I tried to add indexs on the STATE in both the bank and census table but it didn't seem to affect the peformance.

Is Mysql not made to handle 10s of millions of records? Is 10 minutes not long on this query? Am I properly indexing?

If I remove the census table from the equation and just do a straight WHERE things still slog at a slow pace

BigINT Issues..?
I have a PHP script which adds money to a user's bank, the bank feild uses bigInt with a length of 40. bigINT(40).

However, if the bank of a user surpasses 1trillion (1,000,000,000,000) then it resets to 0. I thought bigINT supported far larger numbers? Why is it happening??

Joining Issues
I av a table "code_desc" with the columns: code, desc. an example of a row from it is 01, Industrial Designs respectively.
I have another table with the name q_lists with a column referencing from the code_desc table in something similar to this:

select q.id, cd.desc from q_lists as q, code_desc as cd where q.department=cd.code
//department colums in the q_list table contains int values
//which are fully represented with original values in the code_desc table.
//this saves space

Lately i found the need of adding 1 more column (ministry) to the q_list table referencing from code_desc just the same way as the department column

The problem now is how to retrieve the real values of both from the code_desc table in the same query such that i av something something like:

id | department | ministry
---+------------------+---------
24 | Industrial Designs | Education