Need For Aggregate Function First?
I have a table, like so:
col1 col2
A 1
A 2
B 4
B 4
and I want to group it on the first col, and for the second column I want to test if all grouped values or the same, if they are I return the value, else i return some default eg. 0
My query should be something like this:
select col1, if (count(distinct col2) = 1, first(col2), 0) as col2
from table
group by col1
-> the aggregation function first does not exist..but I need it, with what can I replace it?
View Complete Forum Thread with Replies
Related Forum Messages:
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. =....
View Replies !
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.
View Replies !
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?
View Replies !
How To Code Sql Update Using Aggregate Function
These 2 alternative queries express what I want clearly: update phpList_work p, order_info o set p.oid=Max(o.oid) where p.cid=o.cid UPDATE phpList_work p LEFT JOIN order_info o USING (cid) SET p.oid=max(o.oid) they get the error #1111 - Invalid use of group function phpList_work is a list of Customer records. phpList_work already has a subset of the customer list, with ID values (cid) order_info is the order master, keyed by its id (oid) and has cid The invalid query shows I want to select the order_info value of oid that is the MAX(oid) for each value of cid in phpList_work. The manual does not discuss this. If the functions do not support this obvious and useful syntax, how do you do it?
View Replies !
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?
View Replies !
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
View Replies !
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
View Replies !
Aggregate Functions
I was having to atmysqlting to calculate the monthly average of an occurrence over a 4-year time span. Here's the basic query: SELECT t1.county,t2.year, t2.month, count(*) as total FROM t1 LEFT JOIN t2 on t1.year = t2.year AND t1.month = t2.month GROUP BY t1.county, t2.year, t2.month The output I get looks sort of like this: county | year | month | total 01015 | 1999 | 10 | 2 01015 | 1999 | 11 | 3 01015 | 2000 | 2 | 5 So here the t1.county variable contains over 500 unique items and I'd like to list a monthly summary for each one, including the months which are not contained in t1.month. I created a separate table (t2) with all the possible months and years and thought that a LEFT JOIN would do the trick. But, maybe because the t2 table doesn't contain a county variable with each combination of year and month it doesn't work right?
View Replies !
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]...?
View Replies !
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
View Replies !
Double Aggregate SUM And COUNT
I am trying to calculate the total amount to charge per customer. I have a customers table and an orders table and a devices table. Basically it works like this: Get the price per unit (price_per_unit_per_order) out of the orders table. Get the total number of devices in the current order (total_devices_per_order). SUM(total_devices_per_order * price_per_unit_per_order) AS total_per_customer while the customer is the same. Basically there can be multiple orders for the same customer each with different order prices, but I only want one total number per customer. Here is what I have: SELECT SUM(COUNT(o.order_id) * o.price_per_unit) AS total_per_customer FROM customers c, orders o, devices d WHERE c.customer_id = o.customer_id AND o.order_id = d.order_id AND d.active = 1 GROUP BY c.customer_id, o.order_id ORDER BY c.customer_id, o.order_id ASC
View Replies !
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.
View Replies !
Aggregate Functions In Nested Set
I'm trying to make work a simple query that extracts from the db all the categories that contains at least one product and show the total number of products contained in each node, ignoring invisible categories (and products inside them). This is the query: ....
View Replies !
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.....
View Replies !
Aggregate 15 Minute Data Into Hourly
- Running MySQL 5.0.51a on FreeBSD - I have data like this in my database: +---------------------+----------+ | datetime | NUMBERS | +---------------------+----------+ | 2008-06-06 12:15:00 | 23 | | 2008-06-06 12:30:00 | 9 | | 2008-06-06 12:45:00 | 47 | | 2008-06-06 13:00:00 | 62 | | 2008-06-06 13:15:00 | 78 | | 2008-06-06 13:30:00 | 68 | | 2008-06-06 13:45:00 | 47 | | 2008-06-06 14:00:00 | 56 | +---------------------+----------+ Which I need to be aggregated to hourly data - like this: +---------------------+----------+ | datetime | NUMBERS | +---------------------+----------+ | 2008-06-06 13:00:00 | 141 | | 2008-06-06 14:00:00 | 249 | +---------------------+----------+
View Replies !
Hide Aggregate Columns For A Subquery?
I'm trying to form a query that will use a aggregate function in a subquery, but it isn't the aggregate function that I want to use, but another column. Here's the code: SELECT * FROM member_data WHERE id = ( SELECT member_id, SUM(active) AS listnum FROM email_lists GROUP BY member_id HAVING listnum = 0 ); This code won't work, because there are two columns in the subquery, the one I need for the WHERE clause is member_id, but I need the SUM column to pick out the right data. Is there any way of hiding the SUM column so it can be used by the HAVING clause, but only produces a result set with one column?
View Replies !
Sub Query - Aggregate Fields Based On Min N Max
In the Users table below there are duplicate users by email address +---------------------+------------------------------+-----------------+-----------+ | ts | email | field1 | field2 | +---------------------+------------------------------+-----------------+-----------+ | 2009-01-31 06:51:14 | user1@rediffmail.com | 05 | 03 | 2009-01-31 16:07:39 | user2@yahoo.com | 02 | 02 | 2009-01-31 16:15:02 | user2@yahoo.com | 09 | 04 | 2009-01-31 16:16:00 | user2@yahoo.com | 06 | 08 | 2009-01-31 16:19:52 | user2@yahoo.com | 01 | 09 | 2009-01-31 02:04:36 | user3@rediffmail.com | null | 01 | 2009-01-31 02:12:34 | user3@rediffmail.com | 01 | 03 | 2009-01-31 02:20:31 | user3@rediffmail.com | 08 | null +---------------------+-----------------------------+--------------+-----------+ I want to fetch one record per user ‘user1,field1,field2’ For user 1 select field1 where min(ts) select field2 where max(ts) the final output should be user1,05,03 user2, 02, 09 user3, 01,03 (max of ‘field2’ is null so it should pick the field value which matches the next min ‘ts’ val)
View Replies !
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) ?
View Replies !
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
View Replies !
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..?
View Replies !
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)
View Replies !
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.
View Replies !
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'.
View Replies !
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?
View Replies !
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?
View Replies !
SOUNDEX Function
I'm using SOUNDEX mysql function to find similar sounding names from a table with 2 million distinct names. Unfortunately there is a single soundex code for every 200,000 names! Meaning there are only 200,000 distinct codes for 2 million entries. Is there any other function / library / technique to work around this immense 1:10 redundancy ? for example, soundex for 'avis', 'apex' and 'apps' is A120, but I'd like to differentiate between them in my search - meaning implement a "stricter" sound comparison than the soundex function offers.
View Replies !
MBREquals Function
Has the MBREquals function been implemented in the latest 4.1.1-alpha build. I am finding problems with that command. It says that problem is there in sql syntax. If it has been implemented. Can you please help me in the syntax of the command.I am finding the same problems with the Intersection,Union,difference,symdifferenc commands But manual says that they are not yet implemented. So I guess they are not yet implemented.
View Replies !
Equivalent Function
I use the following function in Oracle SELEC T decode(status,'A','Active','L','Active','Former') FROM Table What it means is: if status = A, return "Active", if status=L, return "Active", else return "Former" in the select statement. Decode in mysql has nothing to do with this functionality and I didnt see a function while browsing the docs online that did this. Is there an equivalent function in mySQL?
View Replies !
CONCAT Function
I'm needed to insert large BLOBs into a database. With the 1MB packet limit, sending larger amounts of data would be difficult, so I had a neat idea. I would do an initial insert of an empty record and get the auto_insert ID from the response, and then loop through, appending data to the record. My table is simple. One unsigned int auto_increment field (DataID), and one long blob field (BinaryData). When I loop through the data to send, I run: UPDATE BinaryTable SET BinaryData=CONCAT(BinaryData, 'My binary data here') WHERE DataID = 35 The binary data I insert I escape null characters, backslashes, single and double quotes. The data seems to insert fine. The problem is that as I increase the amount of data in the field, CONCAT seems to drop all but the last 416k of the data. Thus if I loop through adding 400k blocks at a time (Which I do) I am left with at most 800k of data in the blob field. Using 4k blocks I end up with 419k of data in the field when all is said and done. Please let me know if/when this will be fixed, and if there is a work around that might be used, r a better way to insert BLOB data is known.
View Replies !
CONCAT Function (bug?)
I'm needed to insert large BLOBs into a database. With the 1MB packet limit, sending larger amounts of data would be difficult, so I had a neat idea. I would do an initial insert of an empty record and get the auto_insert ID from the response, and then loop through, appending data to the record. My table is simple. One unsigned int auto_increment field (DataID), and one long blob field (BinaryData). When I loop through the data to send, I run: UPDATE BinaryTable SET BinaryData=CONCAT(BinaryData, 'My binary data here') WHERE DataID = 35 The binary data I insert I escape null characters, backslashes, single and double quotes. The data seems to insert fine. The problem is that as I increase the amount of data in the field, CONCAT seems to drop all but the last 416k of the data. Thus if I loop through adding 400k blocks at a time (Which I do) I am left with at most 800k of data in the blob field. Using 4k blocks I end up with 419k of data in the field when all is said and done.
View Replies !
A Date Function
I read that MySQL needs dates to be in the form YYYY-MM-DD before you can store them in a table. Since users probably won't enter dates in that order (more likely MM-DD-YYYY or MM/DD/YYYY) is there a MySQL date function that I can apply to the date before it is inserted into the database to format the date correctly? (or does the order of the elements have to be taken care of by the application prior to the database query?)
View Replies !
Add_date Function
I am running the query: SELECT DATE_ADD('pubdate', INTERVAL 3 YEAR) as new_date, pub_autokey from pubs but it returns a null value. What am I doing wrong? In the bigger picture, I want to take a date from pubdate column, add 3 years to it, then save that return value in a different column named pulldate. I am unsure how to do that - can I nest select and insert statements? Something like: INSERT into pubs set pulldate='(SELECT DATE_ADD('pubdate', INTERVAL 3 YEAR) as new_date, pub_autokey from pubs)'
View Replies !
Count() Function
I don't know why I cannot use the count function in mysql. I used the following query at the mysql prompt, but it gave me the syntax error at Count(*) SELECT QProgram, COUNT(*) FROM ContactUs GROUP BY QProgram;
View Replies !
Function Error
I´m trying to create a function using SQL language however even the command below that I found in the MySql docs is not working, I always get the same error whe I try to create a function Any suggestions ???? CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50) RETURN CONCAT('Hello, ',s,'!'); Error: 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 '(s Char(20)) Returns Char(50) RETURN CONCAT('HEllo,',s,'!')' at line 1 (State:37000, Native Code: 428)
View Replies !
Date Function?
I know there is a date function : TO_DAYS but is there a function that will turn a date object into millseconds or seconds?
View Replies !
Dateadd Function
i have a question what do you mean by this:YMD(YEARS(dateadd(month,0,'2006-10-11')),month(dateadd(month,0,'2006-10-11')),lastday(dateadd(month,0,'2006-10-11')))) can somebody explains to me what do this means, is this same as dateadd(month,0,date)?
View Replies !
Preg_replace Function
I am looking for a preg_replace function in mysql....one where I can match a substring within a body of text and replace it with a new substring. I have been looking at both the replace and match functions that mysql offers, but nothing seems to fit the bill. Can someone help me construct a simple query so I can update some text within a field without having to write a php script to do so?
View Replies !
ISNULL Function
In MS SQL I often use the ISNULL function in the select statement to return the non-null field. For example, isnull(field1,field2) would return field1 if there is a value in it but if it is NULL it would return field2. I am now trying to do something similar in MySQL but I see that ISNULL does not work. Is there another easy way to do this in a select statement in MySQL?
View Replies !
LIKE In Search Function
my mysql server experiences really high load average and this is function which is rans most, does anyone know how to rewrite optimize this? Would be appreciated , thanks. if ($q) { $exp = explode(" ",$q); for ($i=0; $i<count($exp); $i++) { $hvahvor .= " && title LIKE '%$exp[$i]%'"; } } $this->get = mysql_query("SELECT id,type,title,url,sname,surl,date FROM $this->mysql_tb_dl WHERE $hvahvor ORDER BY id DESC LIMIT $this->page, $this->limit");
View Replies !
Lookup Function
i have 3 tables; Customer, DVDDetails and OrderForm. In the Customer table there are a few standard fields, including a Customer_ID field set to auto increment and as the Primary Key.In my OrderForm table, i have fields into which data about the order is inputted, but i also have a Customer_ID field.Is it possible to have MySQL 'lookup' the Customer_ID field as assigned in the Customer table and basically paste this number into the Customer_ID field in the OrderForm table?I basically want to be able to associate the Customer's details with their order by the unique order number. the Customer_ID fields are the only 2 common fields between these 2 tables...an easier way to this?
View Replies !
Function For 2 Dates
i have inserted 2 dates say "2008-01-01" and "2008-01-05", I want the return to be "2008-01-01", "2008-01-02", "2008-01-03", "2008-01-04", "2008-01-05" . Is there a mysql function or need a custom function? IF so, can you provide some reference ?
View Replies !
|
TRACKER
