MYSQL :: Create Aggregate Function

IF Function,GROUP BY,aggregate Function Problems
Yep, I have all those problems in the title. So I'll explain each one at a time - I did have another thread relating to this very same query but I thought it was time to update where I am with the query because at the moment I feel like I am getting nowhere!

The query I have basically searches through an items_ordered table through each product and checks to see whether the item is VATable or not. This is not where I have the problem though. Where I am really having the first problem is when I am trying to use the IF function to check if the TOTAL of an order is over Â£300. IF it is then I multiply it my 0.95 (i.e. 5% off). With the query below I get no errors but neither do I get the desired result. It's as though it couldn't even see it. =....

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 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?

How To Create A Function?
i'd like to create a function but i have a problem:

This is the script:

use DB
delimiter $
create function date_flow(isaero char(1), maturity date, freq int(1)) returns varchar(50)
begin
if freq='1' then
select datediff(curdate(),adddadte(prev_date,interval 1 year)) as b;
return b;
end if
end $
delimiter ;

when launching there is an error:
ERROR 1415 (0A000) at line 3: Not allowed to return a result set from a function

i know the problem is the select line but how to solve it?

Create Function
How can I create a function in mysql command prompt because when i try it throw a lot of errors but in mysql administrator they create find and they work find.

CREATE FUNCTION
Sorry for this n00b question but I can't find any material on this matter

As far as I can see I can create my own functions with the Command CREATE FUNCTION

I can see great use of this but to my best knowledge I can't just use this in query. I have to create a script yes?

If I create a function how do I call it in this script?

Replication And Create Function
I'm running 5.0.4-beta with replication, and it works OK. Except...
When I use "create function" in the master, it does not get propagated
to the slave. Is this reasonable behavior? I don't think so. I'm
supposing it is a bug.

Create A Dynamic Alias Name Using A Function
Is this possible?

SELECT

sum(if(EXTRACT(YEAR_MONTH FROM hitdate) = EXTRACT(YEAR_MONTH FROM
now()),1,0)) as EXTRACT(YEAR_MONTH FROM now())

FROM TABLE_NAME

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 Functions
Are aggregate functions fast when used with large sets of rows?

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

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 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

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.

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) ?

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?

Where Clause Issues When Selecting Between Dates With Aggregate Functions
I am working on a reporting tool that scrapes ebay feedback scores and enteres then into a database to be used later in various reports. One report I am trying to create will display how much a user's feedback score has increased over a specific period of time (e.g. last 30 days).

There are two tables being used in this query. One simply lists the the identities being tracked (identities) and the other records their feedback score (feedback). Each day a new row is created with each identity's current feedback score.

When I try to get the total increase in feedback score since we started recording data it works just fine:

SELECT i.name
, MAX(f.feedback) - MIN(f.feedback) AS calc_feedback
FROM identities AS i
LEFT JOIN feedback AS f ON i.id = f.identities_id
GROUP BY f.identities_id
ORDER BY calc_feedback DESC
, i.name ASC
But when I try to add a WHERE clause that specifies a specific time period to pull this data from it doesn't return any rows. It doesn't give me an error, it just doesn't return any rows. Here is the query with the WHERE clause I am using (I've tried various other one similar to this one).

SELECT i.name
, MAX(f.feedback) - MIN(f.feedback) AS calc_feedback
FROM identities AS i
LEFT JOIN feedback AS f ON i.id = f.identities_id
WHERE f.feedback BETWEEN NOW() AND NOW() - INTERVAL 2 DAY
GROUP BY f.identities_id
ORDER BY calc_feedback DESC
, i.name ASC

Create Another Field After Create Table
i have create a table from CLI, i would like to add another field after create table.
How to do that ?

How To Create Different Namespaces When Using CREATE VIEW?
I'm doing a triple join between tables which have similarly named fields...I was hoping that when using CREATE VIEW, the result would be a table with fields such as a.field, b.field, c.field...but I get an error "Duplicate column name 'field'"

...How can I get around this besides changing the name of the duplicate field in each table?

Could Not Connect To The Database, Please Recheck The Settings You Specified. Also Make Sure That A Database With The Name You Specified Already Exists On The Database Server. PhpAdsNew Will Not Create The Database For You, You Must Create It Manually
The error message I get above is the same everytime when trying to install phpAdsnew. I have tried everything. I am hosted through yahoo! and have actually succesfully installed scripts before even though I know nothing about it. Now that I have a "auto-install", it does not work. Yahoo! also offers php-nuke with the auto-install where you just click a few buttons, and I get a similar error stating that it can not conect to the database.

Please can anyone help me and tell me what I am doing wrong?

Now() Function
I am using now function to get current date when info is added to my table. I am using a hosting provider that is 6 hrs ahead in time zone so i always have to subtract 6hrs to get the correct time. Is there anyway I can format the time to be inserted to my time zone?

Self Run Function/..
I need to delete some records from my table every 5 minutes. I thought that would be better if done via a trigger, function, etc. But I don't know how to fire a trigger every 5minutes or is there any other way to do that?

Using The AVG Function
I am trying to use this query:

SELECT average(price) FROM `data` WHERE id IN ( '3', '3', '6' )

to average the price of items users select. The problem is that when a user selects a value more than once it only includes it in the average once. How can I make it average all the values?

Looking For A Particular Function
I have a Column which consists of only DATE's and an ID code. I want to enumerate all the DATE's of a certain ID to get a grand total of a ID. Does such a function exist?

Avg Function
Why doesn't this work:

SELECT avg(value) as avg_value from playerstats where avg_value = '100' and stattype = 'r' group by avg_value

I'm trying to select the avg_value of 'r' where the avg_value = '100'.

Last Function
How do you get the last row in a grouped query. I have a table with running balances. At the end of the day, I want to find out the last balance.

select *
from accounts
group by date(dateCol)

This always gives me the first row of the grouped column dateCol.

NOW() Function
We are developing a tool here that uses a php script to insert data into a MySQL 5.0.41 database running with the INNODB engine. While tracking a bug we noticed a strange effect.

Each insert query contains a call to the NOW() function to store the create data of the record into a field automatically. Therefore we can expect in my opinion that the timestamps of the records are increasing simultaniously with the autoincrement key of the table. This actually is not the case. On the contrary the timestamps are jumping up and down with large differences. For example we have the record with ID1 that got the timestamp 14 minutes 2 seconds where ID2 got 15 minutes 15 seconds and ID3 14 minutes 9 seconds. How could this be? Does it mean that the server does not execute the statements in the order he receives them? Is it possible that it took more than a minute to execute a simple insert statement?

Max Function
I have a table with name, priority and parent as three columns...I want to get the max(priority) grouped by the parent... I am trying to get name, priority, parent, max(parent) as the four columns in the result...Could some one help me with this

select name, priority, parent from table1
select max(priority) from table1 group by parent

are the two select statements that I wish to combine...is it possible..?

Function In C
I was trying to figure out how would you write a function in MySQL using C. I saw that theres a CREATE FUNCTION syntax but am confused on how to compile and write a C function.
For example if I wanted to write a function that added an extra period after every period and called it ADD_PERIOD() how would I do that.

Example:

x = "joe runs far."
SQLSTRING = "SELECT * from Story where Sentence = '"+ADD_PERIOD(x)+"'";

this would return. joe runs far

SQL IN() Function
so i have a query such as
SELECT first_name FROM users WHERE user_ID IN (5,4,8,19,8,4) and i want the first_name to be retrieved for EACH of the cases (eg. name is repeated twice for 4 and 8)

2 Queries In 1 Function?
Is it possible to query the database twice in the same function?

If so is it possible to use results form the first query IN the second?

Count () Function
Cant anyone tell me why this very basic query won't process.
This is to give a breakdown of Mem.heard 1-12 numbers ("where did you hear about us survey") for ea of the MOrig two user types

EX:
MOrig | heardof | count_heardof

UserA 1 5 (times)
UserA 2 6 (times)
UserB 1 3 (times)
UserB 2 8 (times)

SELECT MOrig, heardof, count ( heardof ) as count_heardof
FROM Mem
GROUP BY MOrig, heardof
ORDER BY MOrig

Search-function
Does anyone happen to know how to select info from the database based on multiple strings like so:

SELECT title FROM food WHERE title LIKE '%New%' AND WHERE title LIKE '%York%' AND WHERE title LIKE '%restaurant%'

SUBSTR() Function
SELECT SUBSTR(message, 0, 20) FROM posts

My goal is to start at the first characters on the far left, and then return 20 chars only. This function for some crazy reason seems to start at the end of the string though, which is stupid, because how do I know where the string starts?

Hidden_field Function
Does mysql 4.0.24 standard have a hidden_field function? I need to maintain state from one form to another and hidden_field is used in a script I have using mysql 3.22. If 4.0.24 doesn't have hidden_field function, what can I use with mysql 4.0.24, which is used on my server?

Date Function
I have a time stamp field in my orders table and I want to form a select statement where it returns all orders with an order time of two weeks ago from the current time or later so I can keep better track of unpaid orders.

Here are the fields for the orders table
orderID
order_time
order_status
etc...

SELECT orderID FROM orders WHERE ???? AND order_status=2 ORDER BY order_time ASC

Thanks for any help. The date functions are a little confusing to me from the manual.

Function ROW_INDEX
I couldn't find a function that numbers the resultset of a query. First row should be 1, second row 2 etc.

I searched for rownum, rowcount, rowindex etc with and without underscore's.

Password() Function
I've created a simple table called users. This table contains four columns. First_Name, Last_Name, User_Name, Password. I've used the password() function when inserting a user. I have read the documentation on using other types of encryption, but it's killing me that I can't get this to work. Robert has a User_Name of "Bob" and a password of password('tomato').

===============================
HOW I ADDED BOB

insert into users values("Robert","Evans","bob",password('tomato'));

1 row added yada yada

HOW I QUERY BOB

select * from users where User_Name="bob" and password=password('tomato');

Empty Set

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

That exact same query is in a tutorial for a simple PHP login form, but an empty set is returned even in the command prompt using MySQL by itself, therefore the script cannot move on.

Mysql_query Function
I making a loop of the returned result set from mysql_query, it is very important to know if an update occur during the loop to the table affect the result set of the loop or not ?

I need to loop blindly without see any updates or inserts occur during the loop....

Counting Function
I have a table with a list of names and some are duplicate and I would like to count them?? It's like this:

Drew
Simon
Alli
Tim
Beth
Alli
Simon
Drew
Simon
Simon
Alli

Then to export:
Drew - 2
Simon - 4
Alli - 3
Tim - 1
Beth - 1

How could I go about asking this query?

SELECT COUNT(name) FROM table

??