How To Pass Filter Parameters To GROUP BY (aggregate)
My query is how to pass WHERE x>y filters to GROUP BY (aggregate) query that aren't included in the output field list?
I have a query that returns aggregates grouped by id, it is also limited by dates, but they are no present in the output.
Something like this: ....
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 !
Datetime :: How To Pass Value?
How to pass datetime value to insert in mysql? i need date like this: dd/mm/yyyy, and mysql use date like yyyy/mm/dd.. In delphi 7: This is what i´m trying to do and it´s no working. Parameters.ParamByName('dtche').value:=strtodatetime(FormatDateTime('dd/mm/yyyy',date) + ' ' + FormatDateTime('hh:mm:ss', time));
View Replies !
Storing SHA($pass)
I'm using SHA($pass) to store passwords in mysql. My question is what should the pass colum be, is the SHA always a certain length etc. What is the column type with the least memory usage/problems etc
View Replies !
Create User/Pass
how do i create a username and password for a database? i tried using.... GRANT ALL ON database.* TO user_name IDENTIFIED BY 'password' that gave me a "access denied" error. is that command just to set a users accesss level? is there i different command to make a username/password?
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 !
Reading Auth And Pass From MySQL Db.
I have java program which require authentication. I use this now: <?php validateAuth(); exit(); function validateAuth() { $Registered = array(array("username" => "public", "password" => "auth"), ); $outputString = ''; $username = addslashes(htmlspecialchars(urldecode(strtolower($_GET['username'])))); $password = sha1(addslashes(htmlspecialchars(urldecode(strtolower($_GET['password']))))); for($i = 0; $i < count($Registered); $i++) { if($Registered[$i]['username'] == $username && sha1($Registered[$i]['password']) == $password) { echo("Valid"); exit(); break; } } echo("Invalid"); exit(); } ?> But if I wanna make it to read users from mysql database?
View Replies !
Pass Table Name As Variable In Procedures
i have the following problem: suppose you create a procedure that receives the name of a table as input, and i want select * from the input table : create procedure try1(in table1 char(20)) begin select * from table1; end // mysql answers "table1 doesn't exist" tried to insert the name in a @-variables but doesn't work... Any idea?
View Replies !
How Can Do A Single Pass, String Clean Up?
situation: have full names that could have 1. franklin, joe r. 2. franklin, joe r 3. franklin jr, joe 4. franklin PhD, joe 5. frankin-ross, joe so far i came up with: SELECT distinctrow CASE WHEN (substr(instr_name,-1,1)=".") THEN substr(instr_name,1,length(instr_name)-3) WHEN (substr(instr_name,-2,1)=" ") THEN substr(instr_name,1,length(instr_name)-2) WHEN ((locate("-",instr_name)>0)) THEN REPLACE(instr_name,"-"," ") WHEN ((locate("Jr",instr_name)>0)) THEN REPLACE(instr_name," Jr","") WHEN ((locate("Sr",instr_name)>0)) THEN REPLACE(instr_name," Sr","") WHEN ((locate("III",instr_name)>0)) THEN REPLACE(instr_name," III","") WHEN ((locate("PhD",instr_name)>0)) THEN REPLACE(instr_name," PhD","") else instr_name end as instr_name, instr_name from results where instr_name is not null i want to just end up with franklin, joe or franklin ross, joe meaning, i want to rip out any middle initials at the end of the name, including if there is a period. also, rip out any last name suffix (i.e. Jr, Sr, III, PhD, etc). and remove the hyphens from any person with a hyphenated last names.
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 !
Filter
sample record x id field1 field2 1 a q 2 b x 3 a y 4 b z I want to set a "y" filter on field2 but want to have all records with the same field1 value as result. so, in the field2 = "y" filter, I want record 1 and 3 as result because field1 is in both records "a" (Show me all records with the same Field1 value when field2 is y)Is this possible and if so, how?
View Replies !
Pass Command-line Argument To MySqlDump
I'm running MySQL version: 5.0.15-nt on WinXP-Pro/SP2. I created a one-line batch file DumpPAM.bat in a directory on the PATH: mysqldump pmtallocmodel_development %1%.txt I invoked it from a Command window with the command: dumppam test What I got was: K:>dumppam test K:>mysqldump pmtallocmodel_development 1>test.txt K:> So I did get parameter substution, but I got that gratuitous "1" added. What's the MySQL way of doing this, if it's possible?
View Replies !
How To Make Mysql Require A User And Pass 5.0.x
I signed up with a new web hosting company who offer packages for RedHat on their virtual servers. We just click to install it and then we can use phpAdmin to administer it. Problem is, even after I removed the 2 ANY accounts and root has a password from the installation process. I can telnet SSH into the server and type mysql to gain FULL MYSQL ROOT accesss!!
View Replies !
How To Pass Default Values In A Stored Procedure
can anyone pls help me in passing default values in a stored procedure. That is if no value is passed to the SP then it should take the default value of that datatype.it is working in sql...but what is the alternative for that in mysql? my sql synatx will be something like this.. ALTER PROCEDURE [dbo].[usp_select_projectparticipants] ( @ProjectId INT=NULL what is the alternative in mysql?
View Replies !
How Call/pass A DDL Script In MySQL Console?
I started successfully the MySQL console mysql.exe. How can I call (and execute) from this MySQL console an external file (say D:myprojmyinit.SQL) which contains DDL statements? Secondly how can I pass the same file as a parameter to mysql.exe when I start the console?
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 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 !
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 !
Filter Record
i come across problem where the SQL returns only exact match. But i need to return data with similar match. I have table A and table B. I need to match data in table A with any possible value in one of the field in table B. For instance, i need to get data (*) in table A where College match Victoria College in colum CollegeName. It should return rows from table A including: The Victoria College Victoria College, The victoria college, ptd ltd.
View Replies !
My Sql Filter Data
I'm store timestamps data and want to get different month data. that mean every 5min save data and I want to get that as 5 month bundle. And want to show that as 5 different months. I think want to devied that data set for 5 month.
View Replies !
Filter The Results
Most of the time I only need a small set of rows or columns from the results of a SHOW command. So I'd like to do something like SELECT Rows FROM ( show table status from my_database) WHERE Name = 'my_table' I've looked through the docs and can't find any valid syntax for this type of operation. I suspect it's not possible
View Replies !
Swear Filter
I was looking for a good swear / rude words filter but couldnt find anyone who'd got a simple SQL file for download - so I made one and invited people to add their own rude words..... the result is here:
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 !
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 !
Automatically Filter Records
I was wondering if there is a way to automatically filter records based on a mysql userlogin name?? I have serveral databases that I want to combine in order to manage the databases more efficiently. - I'm currently using MySQL 4.1.12 and I'm currently testing 5.0.14 - I have a databases for each user account. - Each database has the same schema and entity relation model. - I have to provide raw access to the database via various connections (odbc, mysql clients) Example, when the user logs in, via any applicaition/odbc connection/ mysql utiltiy, MySQL server sets a variable DatabaseAccount=$loginusername, where DatabaseAccount is a table column in all the tables. The server only returns records that match the DatabaseAccount name. I think this would be a namespace but I did search for "mysql database namespace" in google and got a bunch of .Net stuff back.
View Replies !
How To Create A Filter Layer Before The Db
i want to know how and in what language i should write a filter layer before querying mysql. i want to send to this layer all the query's (update insert and select) and this layer will bring back errors (like if the query have a wrong table name or bad col name) and after the query was checked it will query the DB and return the result from the db to the user. i want it super fast and stable. in what language i should write it? (C? C++? perl?) the server is linux or freebsd i haven't deiced yet
View Replies !
Filter Fields During Import
I have fixed width txt files that I'm importing into MySQL. I can import the files into MySQL, but I only need a small portion of the rows in the file provided by my wholesaler. For example, I have a fixed width file that has 70 fields for 1.7 million products (rows). I only need about 100,000 of those products. I've been importing the entire table and then deleting all products that have my product type flagged as "Yes" (leaving me with about 100,000 rows). Is there a way to filter my import as its coming into my database (rather than updating the table after its been imported)?
View Replies !
Filter By An Empty Column
I am trying to get the results from all the rows where the email column is empty but it won't work. If I change the column from empty to something like 'NA' it works fine but I need the column to be empty as I use it for all the email addresses of the people & dont want NA in the email address. Here is my sql statement is it correct as I don't get an error but I dont get any results either & there is about 30 email addresses in there & also about 25 that are empty.
View Replies !
Filter Results Of One Query
I'm looking for the syntax that would allow me to filter one querys results using another query i.e. QUERYA = SELECT * FROM table WHERE id < 100 QUERYB = SELECT * FROM QUERYA WHERE id <> 49 I understand that I can do the above example in one query - I'm just using that to simplify matters.
View Replies !
|
TRACKER
