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 Complete Forum Thread with Replies
Related Forum Messages:
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 !
Updating 2 Fields Based On Search
I'm trying to change 2 database fields based on another. I want to update te fields levelend and level (expiration date and membership level) based on the field "gender". So if the gender field reads "K", I want to change levelend to 2556100861 and level to 38 For some reason I just can't get it quite right.
View Replies !
Selecting Fields Based On The Value Of Another Field
My friend asked me to help with this query but unfortunately I wasn't able to figure it our for him. Here's the info he presented to me: Table 1 - portrait field 1 - id field 2 - type (will be the id from a row in business or personal) Table 2 - business field 1 - id field 2 - bizname Table 3 - personal field 1 - id field 2 - firstname field 3 - lastname His PHP code will select a row from portrait where 'id' is a given number. Then based on the value of 'type' for that row he will either select the 'bizname' for that corresponding id or 'firstname' and 'lastname'. There is no way to know ahead of time whether the row is a business or personal account. If this can be done without a subquery, that is ideal. However, if a subquery is required, that's ok, too.
View Replies !
Finding Duplicates Based On 2 Fields
I've got a client that is cleaning out their CRM database. They recently imported a load of files but they had the duplicate checking set so that firstname, lastname, and email all had to match in order for the CRM to declare it a dup. Now they want me flag all the duplicates based just on firstname and lastname. But have no idea how to do it when two fields have to match. Would something like this be a step in the right direction? SELECT uid FROM users_tbl HAVING count(firstname,lastname) > 1; I'm still waiting on some information from the client so I'm not sure what version of MySQL he's using.
View Replies !
Union Based Upon First Query
I have a query that I would like to use a union statement in to grab the number of replies to a specific thread. The initial topic thread is in a different table, which I am grabbing in the initial query... I would prefer to do this in the single query, however I supposed I could do a separate loops and grab the number of replies with a totally distinct query ....
View Replies !
Creating A Query Based On Dates
I am trying to write a query (in PHP) which selects from a database all of the items which are in the future. My query is as follows SELECT * FROM news WHERE ((news.date)>$today ORDER BY date where news is my database, news.date is the the field which holds the date for the item and $today will be replaced my current date. At the moment it seems to display all values, which suggest its not functioning properly.
View Replies !
Query Based On 'does Not Exist' Condition
Let's say I have a table called 'forumtopics' with a field representing the topic author's username. I then have another table called 'ignorelist' which has two fields; one is an account number and the other a reference to the aforementioned author. There can be many ignored authors for any one account. For any given account number, I'd like all the rows from the forumtopics table where there is no match for (account, author) held in the ignorelist table. Obviously it's easy to check for existence but can the opposite be done? I run MySQL 4.0.17.
View Replies !
Query Based On Data Of 3 Tables
this is what I've been trying to implement: I have, say, three tables. One is a user table, with id, username, etc Second is a question table with Question ID, Question, Category etc And third is an "answers" table that keeps answers given by the users. It has the classic id key, and Question id and User Id columns. What I want to do is this: Select a random question of category 1 lets say from the questions table for which user X has no record of answering in the answers table.
View Replies !
Totals Query Based On Days
If I have a table with a ProductID, Quantity, & DateTime field, & would like to have the sum of the Quantity calculated per product per day with blank days being accounted for even if zeroed out, how would I go about accomplishing this in one query? Example result for ProductX:
View Replies !
Conditional Select Based On Query Results
I want to print a different message on the database, if a query returns an empty set and a different if the query returns any records. How can i accomplish that? I looked at the case statement but i can't get it working with that.
View Replies !
How To Query Multiple Tables Based On Value Difference
I 5 tables all with equal columns,but with different values. Per example: time_start,time_end,num_a,num_b,price etc. How can I retrieve the values of all columns `price` between tableX and tableY where time_start in table Y is higher as a datetime than time_start in X. So,the query is about finding the values of `price` between 2 dates in differnt tables.
View Replies !
Query Which Gets Rows Based On A Radius, Lon And Lat Doesn't Work?
I have found this query which gets rows based on a radius. I need this for zip codes based on lon and lat's. $sql2 = "SELECT * FROM table as z WHERE (SQRT( (69.1 * (".$userLat." - z.lat)) * (69.1 * (".$userLat." - z.lat)) + (53.0 *(".$userLong." - z.lon)) * (53.0 *(".$userLong." - z.lon))) <= ".$userRadius." )"; return $sql2; $res = mysql_query($sql2, $connDB) or die(mysql_error()); $row = mysql_fetch_assoc($res); based on a test zip code gives a result like SELECT * FROM table as z WHERE (SQRT( (69.1 * (52.399834 - z.lat)) * (69.1 * (52.399834 - z.lat)) + (53.0 *(4.840762 - z.lon)) * (53.0 *(4.840762 - z.lon))) <= 100 ) the lat and lon of the test zip code are right. As you can see z.lat and z.lon don't get any value. And these would be every lat and lon in table. In my db table lon and lat are decimal(10,6) type with a default value of 0.000000
View Replies !
Howto Make A Query Based On Another Result?
I need to make query from another query result. tbl_A fields: ID and Date tbl_B fields: ID and FileName Code: Select ID FROM tbl_A where Date>=CURDATE() this 1st_query_result will be any ID with current date from tbl_A. Then I need this 1st_query_result to query the filename which store at tbl_B. something like this: Code: select FileName from tbl_B where ID = 1st_query_result
View Replies !
Determining Which Table To Query Based On Data Within Tables
I have 2 tables: default_categories column 1: category_id column 2: category_name column 3: category_parent custom_categories column 1: custom_cat_id column 2: custom_cat_name column 3: custom_cat_parent The custom_categories table won't necessarily have anything in it but if it does, I need to choose the data from the custom_categories table over the data from the default_categories table. So if the default category has 3 rows with IDs | names: 123 | Dogs 456 | Cats 789 | Fish And the custom category has 1 row with IDs | names: 456 | Very Cute Cats I want my query of these 2 tables to produce the following IDs | names: 123 | Dogs 456 | Very Cute Cats 789 | Fish I've tried joins like the one below but they aren't working because if there is no custom_cat_id, it won't give me the result for the default category_id. MySQL SELECT * FROM default_categories LEFT JOIN custom_categories ON category_id = custom_cat_id WHERE category_parent = '' AND custom_cat_parent = '' ORDER BY $order_by $sort
View Replies !
Time Based Reservation System - Only 1 Query Should Succeed
a user can select a time they want and submit the page * The code selects all the bookings for a range of time. * It then counts how many concurrent bookings there are for each hour. * If there's less than the total (4) it inserts another row ( the user's requested booking ) into the booking table. We also notify people if there has been a cancellation This has led to a situation where a few different people are trying to book the same session at the same time, and succeeding! We've ended up with 5 sessions booked In the course of 2 page requests this seems to happen: User1 - submits their booking site selects to see if there's availability ( there is!) User2 - submits their booking site selects to see if there's availability ( there still is!) site inserts User1's booking site inserts User2's booking
View Replies !
Query For Month Data Based On Occurance Against Master List
I have a query running nicely. Now I'm trying to expand it. It pulls the data from a table based on matching the id with another table and part of the grouping is by month and 2 other criteria. Now I'm trying to get the data pulled by month to pull only the info where the id matches the master file AND the FRANID's pulled are the same for each month. I'm trying to get a comparisson across times, but not every month has all FRANID's, depending on when they were entered and I ony want data that exists with matches over set months, starting for now with 12, perhaps also for 6 and 3 next time. I've simplified what I'm doing above, but it is detailed below.....
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 !
Selecting Two Fields In One Query
I have two member tables that i wish to select the names from. They are identical except table name (memberOne, memberTwo). It was a mistake to divide them to begin with but it seemed logical at the time (long time ago) unfortunately. I have a drop down html <select> list working fine with query from one table. How does the query look to select both my tables? List looks like this. Member ID is not important but identical ID's may appear. Code: <select name="memberlist" class="dropList"> <?php $sql="SELECT Name FROM MemberOne"; $result =mysql_query($sql); while ($data=mysql_fetch_assoc($result)){ ?> <option value ="<?php echo $data['Name'] ?>" ><?php echo $data['Name'] ?></option> <?php } ?> </select>
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 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 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 !
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 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 !
Query 3 Fields In Same Table As If They Were One Field
I have a very simple test table which I am using to try and get the query right: id | col1 | col2 | col3 | 1 | 3 | 2 | 3 | 2 | 4 | 3 | 1 | 3 | 2 | 1 | 1 | 4 | 3 | 2 | 4 | 5 | 3 | 1 | 3 | 6 | 3 | 1 | 3 | So the table contains and id field and 3 integer fields - each of the integer fields contains an integer between 1 and 4. What I want to achieve is the value of the most frequently occuring entry across the 3 integer fields, as though all 3 fields were actually one - in my example, this would be 3 as there ar 8 occurenecs. I will also require the lowest occuring value also, in this case it is 4. I cannot work out to query all 3 columns simultaneously to obtain my results - I have been looking at joins but cannot work out how to join within the same table
View Replies !
How Can I Compare Two Table Fields In One Query?
This is my problem: "SELECT * FROM a,b WHERE a.field LIKE b.field " --> this works "SELECT * FROM a,b WHERE a.field LIKE %b.field% " --> this works not My Question: Is it possible to compare two filds with the substitute symbol % if the fields are stored in different tables?
View Replies !
NULL Fields In CSV-output Query...
I'm trying create a query to export to a CSV file (using "SQL Manager 2007 for MySQL" query-builder). The resulting CSV-file needs to be in a format where NULL fields are ignored (simply TAB to next field) rather than represented by the ESCAPEflag-N sequence ie: N. Is there a modifier for the SELECT statement that would allow me to specify how NULL fields are represented in the resulting text file? Something like NULL=""?
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 !
Getting Two Distinct Fields From One Tbl From A Join Query Of Two Tables
Im trying to get distinct (the same row returned only once) user_id and forename from a tbl of users (they are always distinct) where user_id in that tbl equals friend_id in another table friend_id, which 'should' be unique ie there 'should' only be one row of a 'friend connection', but in the case of bugs etc this might not be true, what i want to do is select only the row of tblusers with the user details that are matched by join condition below, but only ONCE for each, ie not in as many times as there are the same connection in tblfriends. For some reason the result i am getting is a repitition of the 2 fields in tblusers (that i am selecting) for as many rows as there are in tblfriends that match the join condition. How can i make it only return a row once?
View Replies !
Create URL From To Database Fields In Query Results
I have a MySQL database with table that works as document library. Table keeps details about docs on our server. There are bunch of fields but the key ones here are FileName, URL and location. I'm using PHP to work with the MySQL Database hosted on a Mac OS X Server. Basically, I have a query set up to pull certain records based on a field called Keywords. On the result page I want a field (or item) that combines the URL field and Filename field that ultimately points to the phycial location of the file. For every record thr url field contents is the same since the physical location of the files are in the same directory on the server. Right now the URL field has http://www.mysite.com/docs/. I'd like the result page to have the filename shown, but that filename would be a link to URL+filename. Is there a way to do this? .....
View Replies !
Updating Multiple Rows With Same Fields (in One Query?)
I have 2 tables here table categories +--------------------------------------- + | cat_id | cat_name | cat_total_articles | +----------------------------------------+ | 1 | PHP | 23 | +----------------------------------------+ | 2 | MySQL | 17 | +----------------------------------------+ table articles +---------------------------- + | article_id | article_cat_id | +-----------------------------+ | 1 | 1 | +-----------------------------+ | 2 | 2 | +-----------------------------+ Now I've changed an article's category from cat1 to cat2, and I need to update cat_total_articles of both cat1 (minus 1) and cat2 (plus 1) in category table. Is it possible to combine the following queries into one statement? PHP mysql_query("UPDATE categories SET cat_total_articles = cat_total_articles + 1 WHERE cat_id = 2"); PHP mysql_query("UPDATE categories SET cat_total_articles = cat_total_articles - 1 WHERE cat_id = 1");
View Replies !
Multiple Search Fields - Resulting Sql Query?
How would I implement a search in which there could be multiple inputs to search against? For example i have a client database which i need to search, the search form has 2 inputs FirstName and Surname. If the FirstName is populated and the Surname is not populated my search would be: SELECT CustID FROM customers WHERE FirstName='$FirstName' If the Surname is populated and FirstName is not populated my serach would be: SELECT CustID FROM customers WHERE Surname='$Surname' If both fields are populated the serach would be: SELECT CustID FROM customers WHERE FirstName='$FirstName' AND Surname='$Surname' How would this be implemented?? Do I need to create a search for each possible situation and then control which one is used by an if statement - i'm guessing not as that would very long winded for a longer example such as searching by address which may have 5 or more input fields, any of which may be populated. If its relevant i'm using PHP also.
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 !
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 !
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 !
|
TRACKER
