MYSQL :: Aggregate Functions

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

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

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.

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?

Using VB Functions In SQL
I have a field named "users" with a list of users:

0001-username1
0002-username2
0003-username3
0004-username4
0005-username5

and I want to sort the query by only the "username" half of it.
ie. (if RS = recordset) right(RS.fields("users"), len(RS.fields("users")) - 5)

what would be the SQL statement to get the desired results?

Min Max Functions
I can't find documentation on this facet of max, min. Consider

id item category price
1 nail fastener 0.25
2 screw fastener 0.38
3 hammer tool 12.98
4 plier tool 6.99
5 level tool 27.99
7 anchor fastener 31.23
6 gum fastener 0.10

want to find max price in each category AND WHAT IT IS.

SELECT
id,
item,
category,
max(price) AS price
FROM
hardware
GROUP BY
category

Is the SELECT correct? Is the item and id returned for the price where it is MAX. It does not appear it works in all cases. Is there a way to put MAX in the where clause like:

WHERE
max(price) = price

Functions
I am developing applications using PHP and MySQL.

There are various functions in MySQL that is also implemented in PHP. Such
as date arithmetic, string, numeric, etc...

Do I get better performance if I do them in MySQL (or PHP) and why?

Functions In MySQL
I just started to use stored functions in MySQL. I want to send a parameter to a function as a string (ex.: 'en') and in the function to have SELECT en FROM one_table. That en is not hardcoded, it must come from a parameter sent in function. Is better to watch the code below (check the update and insert statements, watch for language_)

SELECT getcities(1, 'ro', 'it', 'A string, does not matter', 4)

DELIMITER $$ ....

Nested Functions
I know you can't use Nested GROUP BY Functions in MySQL, and I know you can't use SubQueries in MySQL ... so is there any substitute without having to resort to hardcode (i'm PHPing)?

Return Value Of Functions.
OS: Windows XP Professional SP2
MySQL: 5.0.24a
PHP: 5.2 RC5
PHP mysqli extension: 5.0.22

Does my MySQL function support returning of multi-row, multi-column values like a table of values instead of just scalar ones?

I noticed and coded some MSSQL2005 and Postgre functions that could do the same thing. After going through the MySQL documentation, I do not see such a feature in it syntax. Maybe I overlooked that MySQL do have this feature but I do not know how to implement it.

Use Of 2 Group Functions?
when i use this it gives me an error:

select max(avg(field1)).....

is there a better way to pick the data. i am forced to use MySql 3.x version (cause thats whats there on the server!!!)

Alternative For Functions?
After deploying the website to our clients place of hosting, it turned out that the website no longer worked. The reason for this behaviour was simple: the hosting firm simply refuses to create functions for shared hosting formulas.

Before we switch our client to dedicated hosting, I'd like to ask if somebody here knows an alternative for MySQL functions?

The function itself is a simple insert-delete-return-value function, nothing fancy, but it is called not only from within the web application, but also from other applications.

Stored Functions.
Can stored functions return triggers?

Mysql 5 Functions
I downloaded mysql, I did rpm -e to take out the 3.22 from my box (
mandrake 9.x)
I put MySQL 5, because someone told me it supports at last functions..
If anybody knows, can someone tell me, whether I can make functions like
in sql server of microsoft or not?
I want to implement some basic statistical functions like mean ,
deviation e.t.c.
But what I found on net is about UDF and staff for compiling mysql again
for native code...
I didn't find much for functions...
some examples also for procedures didn;t work.

Recursive Functions
Can you create recursive functions or stored procedures?
I did make a simple stored procedure. But once the recursive querry was called,
I got:
ERROR 1424 (HY000): Recursive stored routines are not allowed.

Nesting In Functions
Can ifs be nested in functions or is only one level allowed?

Too Many Database Functions
if I have an information retriever collecting information of the visitor on every page, will it slow the site down.

Question About Functions
i tried a little but don't get it.
i have a table with items with bool values like 'knight', 'ranger', 'wizard', etc... a item can have multiple of these set.

now i want make a function that returns a list of what values are set to true.

a graphical example...
item | knight | ranger | wizard | function should return
---------------------------------------------------------
sword | true | true | false | 'knight,ranger'
staff | false | false | true | 'wizard'

is this possible in sql?...or do i need write a function in my php?....
i even failed at syntax...so maybe a little example would help a lot.
i just need a starting point.

Procedures And Functions
I'm working on a text-based MMORPG using a sql server. I currently have the sql queries for the commands stored in C++ strings but I want to move the queries into the database itself so the C++ will just be a wrapper to set the arguments as user defined variables and search the database for the appropriate query.

I've tried the following for a look at an item command

[Edit: minor inability to add complete information, I did have ; in there, just didn't paste em in]

CREATE PROCEDURE CM_LOOK (OUT output VARCHAR(528))
BEGIN
select item.description into output from item inner join item as Player where item.id=@ARG1 AND item.container=Player.container AND Player.id=@PLAYER ;

and

CREATE FUNCTION CM_LOOK () RETURNS CHAR9255)
RETURN select item.description from item inner join item as Player where item.id=@ARG1 AND item.container=Player.container AND Player.id=@PLAYER ;

[edit: also this is the point it complained about syntax at, I couldn't get to the END]

but both complain about syntax. Am I going the right direction and just need to know the proper format or should I be trying something else for this?

String Functions
Not sure if this is a MySql mistake, but it seems to be. I am trying to execute a query that returns all the records where the first 3 characters of the username are equal to "cd-". After many successive, frustrating combinations of different string formatting / comparison functions, I figure I need some help. Here is my code.

$sql = "SELECT username, officer1, officer2 FROM login WHERE SUBSTR(username, 0, 2) = 'cd-'";

here is my error
Parse error: parse error, unexpected T_LNUMBER on line blah.. blah..

My questions are as follows...
1. I know the code doesn't work, but is this a legal use of the SUBSTR() function. If so... will it (hopefully not) modify the contents of username?
2. In a previous version of this code i used LIKE intstead of =, without the SUBSTR(). Can anyone explain to me how the 'wildcard' characters % _ work. The manual was vague at best.

Date Functions
This time i looked on the mysql site first, found the answer but it doesnt work
I am tryin to get the days between two dates with the following:

SELECT DATEDIFF(now(), date_joined) AS 'joined', DATEDIFF(now(), trial_end_date) AS 'trial' FROM JSPCustomers WHERE username like 'qwerty'

and i get the folllowing error:

Syntax error or access violation: You have an error in your SQL syntax near '(now(), date_joined) AS 'joined', DATEDIFF(now(), trial_end_date) AS 'trial' FRO' at line 1

Custom Functions
is there a way I can make custom functions in mysql?

I need a function that will
replace all
& with blank
all " with a blank
all > with a blank
all / with a blank
and a few more

UDF Functions To DB Server
I have downloaded set of files regarding the MEMCACHE in MYSQL stated from the below link.
http://capttofu.livejournal.com/5091.html
Now i need to check that by installing those to my MYSQL DB Server, can some one tell me how can i install those to my DB server

Date Functions
I've read the Data and Time functions docs and any other relevant docs that I could find, plus tried googling, but I don't see the kind of examples I'm looking for. Can anyone see whats wrong with this mysql query?
Code:

mysql> SELECT id, file, alias, MONTH(mydate) AS thismonth, DAY(mydate) AS thisday, YEAR(mydate) AS thisyear, catagory FROM news WHERE id=6;
ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(mydate) AS thisday, YEAR(mydate) AS thisyear, catagory FROM ne

the following query works so I don't know why this one MONTH/DAY/YEAR one doesn't.

Code:

SELECT id, file, alias, DATE_FORMAT(mydate, '%m.%d.%y') AS newdate, catagory FROM news WHERE catagory = 'special' ORDER BY mydate DESC

How To, Mysql Date Functions
I have a voting system and I want to limit how often a user can cast votes, like make them wait 20 seconds between casting votes.
I can do this with php date and mktime and came across the mysql date functions.

I was wondering if there was a way to achieve the delay using something like "interval" or "extract" to make sure 20 seconds has passed but I am not really making any sense of the documentation.

It needs to have the ability to return true if it is todays date and another true event if 20 seconds has passed.

my timer table has username and timestamp.

Date Functions In Group By
I've got a table with visitor statistics where one of the fields is a timestamp generated with UNIX_TIMESTAMP(). Each hit on the website is recorded as a row.

Now i'd like to get the busiest day, the day with the most hits. Is this anyway possible with the Date and Time functions from Mysql? GROUP BY DAYOFYEAR(ts) or something similar?

Using Time Functions Within A Query
I'm currently working on a script that searches through a database of restauarant information. One of the things it does is allow the user to search only for restaurants that are open at the time of the search, however i get errors when trying to implement this.

Here is the query im using:

Date And Time Functions
I have been having problems using date and time functions in PHP. I am trying to design a schedule with a Calendar. I tried to use the following line of code to output the current month i.e. February. The line of code I have used is this :

$month = mysql_query("select monthname(curdate( ))");

I am getting the following output from this:

Resource id#6

why I am getting this output and are there alternative ways of getting the information I need.

Stored Procedure And Functions?
I would like to ask some questions about stored procedure and functions.
I know some of their difference when it comes to returning values, stored procedures can return many values while stored functions can only return single value.

I want to know if they have difference when it comes to speed, is stored procedures faster than stored functions?

Follow up question,doing some procedures and functions in my database, does it make my applications fast?

Actually I studied this features hoping to make my applications run fast. So that's why Im asking about the speed.

Group_concat And String Functions
I am running: MySQL Distrib 5.0.15, for pc-linux-gnu on i686

I think I have run into a limitation of the GROUP_CONCAT function but I may really be doing things incorrectly. I hope you can point me in the right direction.

We have a table of installed software that includes fields for: Product, Version, OperatingSystem and MachineName. Any software product can live on several different machines and might have different versions installed on each.

I was hoping to produce a list of products, versions and an aggregate of the os/machine names that have this product installed. I tried: Code:

MYSQL STRING FUNCTIONS?
Table
-----
Id - 1
val - -5

SELECT id,val FROM table;
Id-----val
1------- -5

But is that any way i can get just the value by neglecting the negative sign from the table other than using instr, substr functions. I need the output as,

Id
---
1

Val
---
5

Dump Procedures And Functions
I want to dump only the procedures and functions of my MySQL database. When i use mysqldump --no-create-info --no-data --routines, it dumps procedures, function and also triggers. I do not want to include triggers in my dump file. What is the option for that?

Date Functions, Grouping
I have a table in witch one column is of type "date", the rest of the columns are id, name etc.

I would like to select everything from this table, and then group it according to their dates in a special way. The date column may look like this (when sorted):
2006-01-01
2006-01-02
2006-01-03
2006-01-05
2006-02-02
etc

If there is date diff greater than 3 days between two "rows", I would like them to be treated as two different "groups" when the result is displayed.

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.

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.