SELECT Count(something) As Amount WHERE Amount = 'this'
SELECT search_query, COUNT(search_query) as count
FROM hr_search
WHERE count >= 2
GROUP BY search_query
ORDER BY search_query ASC
I'm building a search cloud and this is the query I want to use to grab all results from my database with more than two entries... however SQL doesnt like me using 'count' in the WHERE statement. Is there any way around this?
View Complete Forum Thread with Replies
Related Forum Messages:
Max Amount &
i have a table of salaries that includes year, salary and id of employee. what i want is an output of who was the highest paid employee for each year. how in the world do i approach this? i've attempted some queries from the manual section 3.6.4 but have come up extremely short. i get results where the employee id does not match the employee who was the highest paid for that year.
View Replies !
Variable Amount
i'm developping a php-application that needs to be highly customizable. On the one side, the user has a input-mask, where he can edit database records. on the other side, the user should be given the possibility to alter this database mask on his own, probably in a special "settings tab" - e.g. adding or deleting fields. My approach so far: I have to tables: one, which holds the names of the fields to be offered AND another one, which holds the entries for each "record". These two are linked together by a "right-join" statement.
View Replies !
Certain Amount Of Rows
guess it is a simple question. let's assume i have a certain amount of rows in my resultset, sorted by their id. but actually i would like to retreive only a part of it, let's say the first time the first 20 rows, next time the next 20 rows and so on. think of a forum where you would see a maximum of 20 replies at once in the same thread. below you would have a forward/backward button which would let you flip through the pages with the answers, 20 each. how would such an sql-statement look like? p.s.: one solution would be to retreive e.g. all answers, and just to read out the needed ones from the resultset - but I guess there is a more sophisticated way, which would need less sources
View Replies !
Amount Of Columns
Does the amount of columns in a table matter? My member table has around 30 different columns and i'm wondering whether this is too much.
View Replies !
ORDER BY :: Amount
The following query does not seem to order by "bid_amount". It will simply take the first entry in the database. Any idea how to make it order by "bid_amount" (DESC)? ......
View Replies !
Display Amount/No Duplicates
How do you select only the first #(10, 20 ,30... just some set number) rows in a table. Then if you want to continue, how can you select the next ten. I already know how to set them to display in desc order... but I don't know if adding this code will change it. In case you want to know, I am trying to make a news table and have it display on my web page, but I only want 10 headlines to display at a time, then click a link and the next ten will display. The other question is: How do you test to see if someone has already entered the data you are about to enter. An example would be, if someone entered the data(aa,bb,cc,dd) then tried to enter that exact same data again, it would not allow it.
View Replies !
Percentages And Dollar Amount
what is the best practice for using percentages and dollar amounts? I'm setting up my schema. I will have percentages and revenue to enter into the database. What happens if I have 33% how should this be reprecented .
View Replies !
Storing Dollar Amount With .00
I want to save dollar amounts with two zeros at the end ($5.00). I have used the decimal type and it works if I have numbers at the end ($5.25) but it will not keep zeros. I need this because I want the info to display with two zeros. I saw somewhere about using a FORMAT option, but I can't seem to find in the Manual how to do this.
View Replies !
Variable Amount Of Data
I'm very new to MySQL. I've searched and searched and have been unable to find an answer to my problem. I'm setting up a database that will store photos (not the actual photo but the path) and the names of the people in the photos. I plan on two tables: 1) People * ID * fname * mname * lname 2) Picture * People * Location * Date I cannot figure out how to handle photos that have multiple people in them. The number of people could vary from 1 to 100. How do I create a table where each entry will have a varying number of people listed?
View Replies !
Displaying Certain Amount Of Data
I have created a script. This script of mine takes data off of a table and displays it on a web page. What I want is for the script to display only data that matches the id of another section of the web page. For example, the script would first point to another factor in the database, perhaps the value of a row in another table. Let's call that value 025. In a second table, I have a few rows of data. Their ids were 025, 026, 027 and 028. The web page would only display rows with the ids of 025. However, if that value in the other table was 026, then in the second table, the rows with id 026 would only be displayed.
View Replies !
Code To Pull Amount
I'm certain there is a function to do this but I forget what it is... Say I am pulling values from the database, but I only want to pull like the first 10 chars of the value.. how do I do that?
View Replies !
Limiting Amount Of Returned Rows?
How do I limit the amount of returned rows? Lets say, that I want only the latest 400 rows (of e.g. 4034634 rows) - is there a parameter for that like rownum in Oracle? e.g. select * from someplace where rownum < 400 order by sometimefield?
View Replies !
Limit Amount Of Rows In Table?
I would like to have a table which only holds the last 50 added rows. Table: userips id, userip 4, 22.22.22.22 5, 23.22.22.25 6, 25.22.22.26 | 53, 22.55.55.112 Now if a new ip is INSERTed the last one has to be removed so only 50 rows will be stored at all time.I can do this by first inserting a new ip and then deleting a row which requires 2 query's but is there a more efficient way?
View Replies !
Limit The Amount Of Words To Display
I have the following script: PHP Code: $pressq = mysql_query     ("SELECT Photo, Title, Synopsis, Filename, Brand, Date, RaceProd     FROM PressRelease     WHERE Brand='this'     ORDER BY Date DESC     LIMIT 5"     ); I'd like to limit the number of words in the Synopsis to display only the first 25 words. How do I change the script to reflect that? Right now the 'LIMIT 5' limits the results to a listing of 5 results.
View Replies !
Inserting Huge Amount Of Data In DB
I've got a script that scan .csv files and have to add data in a DB. I've try to save data into array and only insert data at the end in one shot, but I got memory problem. I would like to know the best way to add about 1 million of data in DB. Do I go with one query per data? Do I try to say cumulate like 100 data and then insert them and repeat this until it is done?
View Replies !
Importing Large Amount Of Data Does Not Work
I am importing large amounts of data (over 50 Meg) into a new mysql db that I set up. I use[color=blue] >mysql dbname < importfile.txt[/color] But I keep getting timeouts and errors due to the data being too large. I know that since if I break the imported data into multiple chuncks (by importing a few tables at a time) then everything works.
View Replies !
Large Amount Of Selects Fell Into Sleep
i installed the 5.0.51a community version as service. for some reason the connection fell into sleep after selecting data. (~ 600 SELECT Queries) I dont know why this happens - can someone help me what the reason could be? The server runs on WXP SP2 and the errorlogs are empty
View Replies !
Inserting Money (Dollar Amount) Values
I'm having a problem inserting money (dollar amount) values into a MySQL 5.0 database. I'm using a text box in a ColdFusion web form that allows users to enter the dollar amount for sales. For example, they can enter 1,000.00 for a thousand dollar sale. Everything seems to work fine until the user enters any value that has a comma such as 1,000.00 or 150,000.00 or 1,000,000.00, etc... I've tried using different data types within the database for this column. I've tried Int, BigInt, and Decimal (10,2) but nothing likes the commas. Does anyone know how I can get around this problem?
View Replies !
Selecting Amount Of Rows With Distinct Values In Fields
how do i select the amount of rows in table that have a distinct field value. example: in my table i have a column (column a) that sometimes has duplicate values (sometimes 2 duplicates, sometimes more, sometimes no duplicates). how do i get the amount rows in the table that have a unique/distinct value in their column a (without the duplicates rows)?
View Replies !
Show Amount Of Users On Index Page For PHPList
I have tried posting this on another forum and only got so far, i also tried numerous searches on here and via google, but i can't seem to understand most of the results. What i need is a way to show the amount of subscribers within my database on my index page. So far i have managed to get the below code together but i am not sure where to put the php, at the top in an include etc. At the moment this is not working for me. <?php my_db_sel(); $query_count = "SELECT count(*) FROM phplist_listuser WHERE listid = '1' "; $display = mysql_result(my_query($query_count),0,0); echo $display; ?> I then place <?echo $display ?> where i want it to display.
View Replies !
Command That Lowers The Amount Of Memory That The Process MySQL?
Someone knows a command that lowers the amount of memory that the process MySQL (mysqld, mysqld-nt...) uses??? My work is to execute many INSERT instructions based on SELECT instructions. When the execution starts, all be fine, but the time passes and this process "mysqld-nt" start grow and grow and then the performance be really bad. I tried to use "FLASH" and "RESET" between the INSERTS but no sucess. Someone knows?
View Replies !
To Update A Large Amount Of Values In 2 Columsn In 1 Table
I have a table that I need to change all values for all rows in 2 columns (used for mapping products). Is there any SQL that will let me just say something like, update the 2 columns and insert these values in this order? Example: these are the values: 2, 4, 6, 3, 4, 1, 3, 4 And those values would be inserted as the values for the first 8 rows
View Replies !
Find A Field With "x Amount Of Characters"
Once again I believe I am overlooking the obvious I am trying to create a query, in which the "WHERE" clause looks for a fixed amount of characters in a field. A simple example: SELECT user.id, phone.id FROM 'users' WHERE 'phone.id' (IS ONLY 7 CHARACTERS LONG) I've tried a fixed amount of wildcards (ie: WHERE 'phone.id' LIKE "%%%%%%%") but it doesn't limit the number of characters to 7. Am I missing the obvious - or is this not possible? For the record, I'm using MySQL 5
View Replies !
Row Count Mismatch In Select Count(*) And Explain Select Count(*) From Table
mysql> explain select * from parameter; +----+-------------+-----------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | parameter | ALL | NULL | NULL | NULL | NULL | 3354 | | +----+-------------+-----------+------+---------------+------+---------+------+------+-------+ 1 row in set (0.00 sec) mysql> select count(*) from parameter; +----------+ | count(*) | +----------+ | 97 | +----------+ 1 row in set (0.00 sec)
View Replies !
Count(*) In A Select Returns "1". It Should Behave Like Select Count(*)
i'm trying to make a query work properly but I got lost: SELECT *,count(*) FROM cancons c, musics m, discos d, r_discos_cancons rdc WHERE c.c_id_music = m.m_id AND rdc.rdc_id_disc = d.d_id AND d.d_id_music = m.m_id AND m.m_id = 24 GROUP BY c_id note: cancons (ca) = songs (en) discos (ca) = cd's (en) music (ca) = musician (en) don't worry for the WHERE part. i need it because of the foreign keys. this query returns a table with the title of the song and some more data. on the right side, I get another column called "count(c_id)" with the number "1" in it for each row. That's supposed to be due to the "group by" clause, I think. I'd like to get the table with the songs, as usual, and, with the same query, I'd like to get the total number of rows selected.
View Replies !
Select Count Need Even 0 Count
I know I have seen the answer to this on here before (I think). I have a query that does a count and a join. I want to return records that are in one the one table and then their counts of how many times they have been clicked on, even if that count is 0. Here is what I have: Code: SELECT i.vin , count( i.vin ) AS clicks FROM inventory i RIGHT OUTER JOIN statistics s ON s.vin = i.vin WHERE i.active = 1 GROUP BY i.vin ORDER BY clicks ASC
View Replies !
SELECT COUNT(*)
I heard SELECT COUNT(*) can take a lot of resources if your counting a table with lots and lots of rows (hundred thousands, millions). What if you add A WHERE clause to it? So something like: SELECT COUNT(*) FROM table WHERE pid = ? (pid is a index too btw) IF adding a where clause, does it still scan the entire million plus rows, or only scans what is returned from the WHERE clause?
View Replies !
SELECT COUNT Or SUM()
Is there a difference between these two functions when your trying to gather how much a item appears in a database? For example, which one will be better for a database w/ ~150,000 rows. SELECT COUNT(category_id) FROM Threads WHERE category_id = '2' Will return ~50,000 rows matching category 2 OR SELECT SUM(category_id = '2') AS total FROM Threads total will be ~50,000 rows also.
View Replies !
Select *, Count
I want to perform a select query, but it doesnt return the data i have in mind. The result should be all info in 1 row with the highest date plus a total of game_id This is what works best sofar. SELECT * , COUNT( game_id ) FROM `battle` WHERE game_id =4 GROUP BY game_id ORDER BY b_date DESC LIMIT 1 Yet this doesnt return the last date but the first. Everything else i tried the count function returned 1 even though it should be 20 for example. How can i solve this?
View Replies !
Select Count(*) > @foobar
i have a query as follows and is unsure whether it is mysql or php: $result = mysqli_query("SELECT COUNT(*) > @foobar AS status ....") where it's suppose to get a one row value of either '0' or '1' (false or true) if i were to do a SELECT COUNT(*) FROM... i can use the php $row["COUNT(*)"] to refer to the index. if i were to do a SELECT COUNT(*) AS status FROM... i can use the php $row["status"] to refer to the index. but in this case SELECT count > @foobar AS status, refering to $row["status"] gives a empty value. however, running the same query in mysql command prompt produces +------+ |status| +------+ | 1 | +------+ php doesn't support such select statement? even array_keys($row) returns empty.
View Replies !
SELECT COUNT (*) FROM Table
Newbie: SELECT COUNT (*) FROM table When I run the above query from the command line, it works fine. When I run it from my powerbasic program, I can't seem to get a reasonsible result. What type of result does count return? I get something that looks like this: [ CHR$(133) ][ CHR$(21) ][ CH... and the type is given as -5... What is this type?
View Replies !
Select Count Syntax
im just a begginer in mysql. I have a problem on how can i place a result of a select statement query in a variable. Here my select statement: Select count(employee) from employee where empmonth = 1 and empyear = 2005; This syntax is ok, it return a result of 123894 records. But what I want is to place the result in one variable that i can access inside the program so that i will run this select statement only once. I have tried this one syntax: Select count(employee) into ll_count from employee where empmonth = 1 and empyear = 2005; but it return a result of zero record.
View Replies !
SELECT From 3 Tables (SUM, COUNT)
I have 3 tables: table1: stores data with unqiue data_id number table2: stores comments on the data, data_id is repeated table3: stores votes on data_id table1 example: data_id - name 1 - joe 2 - peter 3 - mark table2 example: comment_id - data_id - comment 1 - 1 - joe is the best 2 - 1 - joe is not that good 3 - 1 - joe is bad table3 example: data_id - vote_up - vote_down 1 - 1 - 0 1 - 1 - 0 1 - 0 - 1 I need to have a SELECT query which selects joe's name, number of comments on joe (3), number of vote_up on joe=(2) and number of vote_down on joe=(1) so from table2 a COUNT(comment_id) should occur, and on table3 a SUM(vote_up) and SUM(vote_down) should occur, I just don't know how to write the select statement ...
View Replies !
Two Table COUNT/select
I need to count the total (using SUM) from one column in one table, where the a condition is set from another table. so far I've been striking out with this: Code: SELECT maxbid SUM(maxbid) FROM `probid_auctions` WHERE probid_categories.parent = 1853
View Replies !
|
TRACKER
