MYSQL :: Show Rows With Result 0 For 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.....

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

Result Sets In Triggers, Stored Procedures, And Functions
I am running MySQL 5.0.27

I need to know how to deal with result sets in pure SQL functions/stored procedures.  I will explain:

I am  working on building a set of triggers for a relational database.  Essentially, I have two sets of independent tables, and I want to keep them in sync.  I have a set of source tables, and a dynamic number of multiple target tables.  I don't want to have to touch the structure of the tables on either side more than I have to, and I want to do it completely in SQL

Here is the structure of what I have so far:

7 source tables
3 triggers per table (update, insert, and delete)

A stored procedure that gathers all the needed information from all 7 tables, and inserts it into a target middle table that will feed all of the other (multiple sets of) target tables.

I have the first set of triggers for the first table done.  For changes to this table there is a single record that needs to be inserted into the target (middle) table.

Where I am right now is starting in on the triggers for the second table.  The kicker for this table is that, for any one change in this table, there will have to be multiple records updated in the target (middle) table.

I have written a function that gets passed a key, which it can use to run a select query to return all of the needed rows.  This is where what I know how to do breaks down.

How do you execute a query and return the result set to be looped on, OR execute a query, build an array by looping on the result set, and then return the array?

I have done this a lot using other programming languages, but this is the first time I've wanted to do something like this in pure SQL.

Udf Does Not Always Show Proper Result
I am strugling for a week now with a problem of an aggregate function i just coded. It seems to be working just fine most of the time but if i run the query many times sometimes the results do not match.

I am runing mysql 5.0.45. Does anybody have an idea what the problem might be. I get the results from a view constructed out of a union of two simple selects. The last result has also a order by which is unavoidable since the function expects ordered row results.

Show Rows With No Matching Fields
here is my query which probably needs some more advanced join statements:

select * from orders o, customers c, orders_styles os, styles s where o.customers_id = c.customers_id and os.orders_id = o.orders_id and os.styles_id = s.styles_id group by o.orders_id

the problem is that sometimes, because of user input, there will be no matching fields in the tables orders_styles or styles (no rows with matching orders_id exists in table orders_styles to table orders). I still want to show these rows however. Right now, as you can see it only displays rows that have matching rows in orders_styles and styles.

Why Does The Slow Query Log Show More Rows Than Exist?
# Time: 070528 17:14:57
# User@Host: counter[counter] @ localhost []
# Query_time: 3 Lock_time: 0 Rows_sent: 7 Rows_examined: 120647
SELECT SQL_CACHE `webpageUrl`, `webpageName`, COUNT(*) AS `count`, (COUNT(*) / (SELECT COUNT(*) FROM _1_log)) AS `pct` FROM _1_log GROUP BY `webpageUrl` ORDER BY `count` DESC LIMIT 7;

mysql> select count(*) from _1_log;
+----------+
| count(*) |
+----------+
| 111824 |
+----------+
1 row in set (0.00 sec)

How Do I Add Simular Rows In Result?
I am new to SQL but I heard that much is possible, so I dare ask if there is a solution to this:

I have a db with rows like this:

species: aa count 10 areacode 11
species: bb count 2 areacode 6
species: aa count 6 areacode 11
species: c count 7 areacode 11

now is there a query that returns this to me?

species: aa count 16 areacode 11
species: bb count 2 areacode 6
species: c count 7 areacode 11

Join Rows Into One Result
I'm trying to select letters in a word from a table with the ascii representations. I am selecting the letters successfully, in the correct order. The rows returned give me a letter in each row. I would like it to return one row with the joined word.

aka. It is returning
a
r
d
v
a
r
k

I would like
ardvark

This probably has to do with GROUP BY, but I don't know any functions to join characters into a string.

Distinct Result Rows
but I could not find it.how can I get the following query to return just one record rather than all of them...i.e., just list each 'category' and the corresponding 'cat' # rather then all the records

PHP Code:

SELECT category, cat FROM bus_list

Trying To Count The Number Of Rows In A Result Set After Query
The user fills out this form to sign up to the website, the form checks the database to see if the username has already been taken with the code:

$conn = mysql_connect("localhost:3306", "root", "********")
                                    or die ("Error With Connection");
        echo("connected<br><br>");
        $db_sel = mysql_select_db("game",$conn)
                                    or die ("Error With Database");
        $check = "select * from users where 'username' = '$username'";
        $db_sel = mysql_query($check,$conn)
                or die (mysql_error());

Subquery Result Returning Many Rows As Single String
Is it possible to return multiple rows from a select statement as a single string?

Example:

One Field With Multiple Attached Rows, Only Show The Field Once?
I have a table called "Customers", with a field called "Customer name" and a "Customer ID". I have another table called "Projects", with a field called "Project name" and a field called "Customer ID" which references to "Customers"

What i am trying to do is: Select a customer. Select the projects that are related to the customer by comparing the Customer ID field. Code:

MyOLEDB Doesn't Support Commands Like SHOW FIELDS, SHOW TABLES
My code is shown below, I can select * from employees but I cannot get any data back from SHOW FIELDS, I do not receive an error, in fact, something is returned but I'm not sure what. I can execute the command:

SHOW FIELDS FROM test.employees

in the Control Center just fine, even cmd.ExecuteReader will enter reader.Read() once, something is returned but what does it look like? ......

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?

How Can I Determine The Offset Of A Result In An Ordered Result Set?
How can I determine the offset of a result in an ordered result set?

I would like to pass the calculated offset into the limit half of and ordered select statement.

E.g. I have a table that records a id and datetime for captioned photographs. I'd like to show the five photos that were taken after the photo with id=23.

To do that I need to find the offset of photo with id=23 in

select id, datetime, caption from photos order by datetime;

Then I could get the result I want by doing....

select id, datetime, caption from photos order by datetime limit $offset, 5;

I've spent several hours scouring around and found some people with similar problems, but no solutions yet.

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.

Ps -fax Show 1 Thread, Others Show 5
Where do I configure "spare servers"???

Show/not Show Option
I have a php page that displays pictures. The pictures comes from a mysql database. The way I am creating the recordset to dislpay is "Select * from tblehouse" . Now what I want to do is instead of selecting every picture from the database maybe create a new field that will take in the option of being checked or unchecked. Then have the query select only the "checked" to display. So my question to you guys is how would create a field that takes in a check box and what would be the sql statement to query just the checked records.

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.