Query A Specific Day, And GROUP BY
The query below, counts how many records each agent has entered in the previous 7 days and groups them by agent...
Then it also counts a total for all the agents for the previous 7 days as well, not grouped.
How can i query a specific day of the week, say 4 days back, only count that days records and group them by agent?
View Complete Forum Thread with Replies
Related Forum Messages:
Do Not Group By A Specific Column
I got a question how to set up a SQL-Statement... I got 2 fields. One has ids saved (not unique!) and one got answers. |uid|answer| |1 |yes | |2 |yes | |1 |no | |1 |no | How do I count how often EACH uid got which answer? SELECT *, count(*) as c FROM table GROUP BY answer That is what I got till now - but the output here is: |uid|answer|c| |1 |yes |2| |1 |no |2| but I want: |uid|answer|c| |1 |yes |1| |2 |yes |1| |1 |no |2|
View Replies !
Group By Specific Part Of Date
I have a field in a table with the date format. I was wondering if there is a way to group by a specific part of the date instead of the whole date? Meaning I would like to grab all records and group by year in the date column. How can I do that?
View Replies !
COUNTing Specific Value Occurances While Performing GROUP BY
I've been looking over the documentation and artfulsoftware, but can't seem to figure this out. What I'm trying to do is group instances of certain ID's at certain times into specific days, like so: SELECT a.id, a.time, COUNT(a.id), COUNT(a.event_id = 1), DATABASE_A a GROUP BY DATE(hit_time); the grouping works, but I'm also trying to put in the number of occurrences of specific values into a column, as things stand now the third and fourth columns in the select result have the same value no matter what a.event_id is equal to in the original table.
View Replies !
Question About A Specific Query
imagine I've got user that can have visual preference (enabled or disabled) saved on a table. Before a user make change of a preference, there is no row associate to that user and by default, the preference is enabled. Now, is it possible to do a query that will output user that have preference set to enabled (those who have update their preference) while also outputing those who have no row in the table preference? I know it may be not super clear, but I need to know before working on that script. So here a sample to make sure it is all clear: TABLE USER (id - name - email) 1 - NameA - a@a.com 2 - NameB - b@b.com 3 - NameC - c@c.com TABLE PREFERENCE (id - label) 1 - Show warning 2 - Show help TABLE USER_PREFERENCE (user_id - preference_id - status) 1 - 1 - Disable 2 - 1 - Enable So if I want to output user email that show warning, it should give me: b@b.com, c@c.com My problem so far is, how to retreive user that have a user_preference row enabled, and user that have no user_preference row in the same query!
View Replies !
Increment The Specific Field By 1 In A Query
I still do the process of incrementing a specific field in a stand alone apps by query 1: get its value query 2: update value +1 But, i realize that this is really a bad idea when this would be used in web applications... specially the database is in another webhost....(very slow response) i tried (using php function) Expand|Select|Wrap|Line Numbers
View Replies !
Query Result In A Specific Format
I need a query in which will give me a single column result ----------------------------------- | [any column Name] | ---------------------------------- 8:00 AM ---------------------------------- 9:00 AM ---------------------------------- 10:00 AM ----------------------------------- 11:00 AM ---------------------------------- I want the query to return a single column name with multiple rows in return ...
View Replies !
Sql Query To Exclude Specific Data From Resultset
I have got two tables,one customer with fields cust_id,email and another with field cust_id,domname,expirydate. i have this query to combine these two tables to give me email and the expirydate as $sql = "SELECT l.email,s.expirydate from customer l, domain s where l.cust_id = s.cust_id "; I need to exclude those expirydates whose domname contains .np in the text.
View Replies !
MySQL Admin - Lost Thread On Specific Like Query?
mySQL win32 (4.0.22) I am using mySQL Control Admin and seeing a lockup on a thread specific to a like query. select * from table1 where field1 like 'http://h%' limit 100 This request will sit (thread) forever and never return any records. (status : sending...) However : select * from table1 where field1 like 'http://h%' limit 100 returns records immediately, no problems... I have already run : /mysql/bin/myisamchk --sort-recover dbtable1, no errors, no problems... How can I tell what this thread is really doing or if any additional problems exist with this table using myisamchk?
View Replies !
Extending Simple Query With Specific COUNT() From Second Table
Currently i basically do following: ----------------------------------- SELECT cat_id, cat_name FROM categorytable WHERE blah=something ORDER BY pos ASC while(FetchRowAssoc()) { SELECT COUNT(*) as itemcount FROM itemtable WHERE category=cat_id echo cat_it, cat_name, itemcount, blah... etc. } ------------------------------------ I fetch some info from the category table and then I want to know how many items I have in the 2nd table which are associated to the categories in question from the first table. Can this be done with a single query statement, so i get from FetchRowAssoc the cat_id, the cat_name and the count of the items for this category? "SELECT t1.cat_id, t1.cat_name, COUNT(t2) as itemcount FROM categorytable as t1, itemtable as t2 WHERE t1.blah=something ORDER BY t1.pos ASC " <- like this but working, i really don't get how to properly write the syntax there... also didn't found a useful example with googling.
View Replies !
1 Line Query To Delete Specific Records From Multiple Tables
On clients machine, currently to delete on trainee record it runs 10 queries to delete records from 10 tables. At the time of running all queries, server shows (104) Connection reset by peer. An error condition occurred while reading data from the network. I think it because of running 10 queries at a same time. Is there any possibility that through one line of query we can delete record from 10 tables. I've tried following query DELETE FROM table1, table2, table3, table4, table5, table6, table7, table8, table9, table10 WHERE empID = 11; But it gives ' error in query.
View Replies !
Sum()/Group By Query Help
I have tried searching for a solution to this, but I can't seem to find a good set of keywords, so I'm hoping some kind soul will answer it for me. I'm not sure it is even possible, but it certainly seems as though it should be....
View Replies !
Group By Query
I have the following query that is dynamically generated: SELECT did, SUM(weight/3) as didweight FROM documentkeywords WHERE word = 'test1' OR word = 'test2' OR word = 'test3' OR word != 'test4' GROUP BY did ORDER BY didweight the idea is to select keywords and add their weights then devide by the number of keywords, the trouble is i wish to remove items from the result that have a word that is not supposed to be there (eg 'test4'). How can i do this?
View Replies !
Using GROUP BY In Query
I have a table with many fields and among them I have an index called `id` and a timestamp called `timestamp`.I want to be able to select rows according to my WHERE clause, but only access the rows with the greatest `timestamp` for each `id`. As if the table is really only composed of one instance of each value of `id`. So I am using the GROUP BY on `id` to limit the results to one row per unique id.Code: SELECT * FROM `person` WHERE `company_id` = 1 GROUP BY `id` ORDER BY `timestamp` DESC, `last_name`, `first_name` Which gives me one row per value of `id`, but I can't figure out how to get it to order by the latest timestamp in each grouping. The ORDER BY clause in the example doesn't work.I have tried approaches using DISTINCT and HAVING, but I can't seem to get it right.
View Replies !
'Group By' Query Help, Please
I'm having trouble with a query In using in conjunction with a Group By clause. SELECT ur.userID, r.referral, COUNT( r.referralID ) AS overall FROM referral AS r LEFT JOIN userreferral AS ur ON r.referralID = ur.referralID GROUP BY ur.userID LIMIT 0 , 30 The above query gets no more than 30 users, with each row representing a unique user. Each row has a count column that counts the number of "referrals" a particular user has, Group By is used so we only get one row per user. My problem is that say if a user has 10 referrals, the above query will return the first referral made to that user, My question is How can I make it so the above query returns the 10th referral (ie latest) referral made to that user?? I know theoretically all i need todo is reverse the referral column for each user so the last one is diaplyed first but I cannot work it out.
View Replies !
How To Write Group By Query
i create table item-info. i want to view my data in the following way. i write query use group by but i want name show only one time no repeatation name next line. item-info table: id name item price 1 A XX 15 2 B XX 16 3 A YY 17 4 C YY 20 5 B ZZ 50 6 A ZZ 40 View my data in the following way: name item price A XX 15 YY 17 ZZ 40 B XX 16 ZZ 50 C ZZ 20
View Replies !
DISTINCT/GROUP Query.
I have some serious issues with a DISTINCT Query. The table is for logging users. Let's say the table looks like this userID | average | timestamp 1 | 20 | 2008-01-16 00:00:00 1 | 35 | 2008-01-17 00:00:00 2 | 12 | 2008-01-18 00:00:00 1 | 16 | 2008-01-20 00:00:00 What i want to go get is the the DISTINCT userID (every user, but only one time) and i want to get the latest value of the avarage field (so sorted by timestamp desc). I tried many ways to get this but i can't seem to get it to work, DISTINCT is using all the fields i add, and if i GROUP i either have to say like, MAX/MIN and since there is nothing that makes the average increase/decrease by every new post in the database. My third try was to order by the timestamp, but i doesnt seem to work in all cases, only sometimes.
View Replies !
How To Group/merge Query
I'm trying to query from a specific column in the table. But there will be repeated values. I wonder if there's any function I can use to "merge" the same ones and end up just a list of all the non-repeating values. Don't know if I make sense here... hope the following helps: Column A -------- apple pear orange apple orange orange cranberry and I want a query that gives me "apple, pear, orange, cranberry" only.
View Replies !
Difficult Query - Need To Group Results By Id And Sum Values
I have got the meat of this query done but I am facing a problem. I am doing a VAT analysis whereby I have every shoe that is over size 7 I pay tax on and every shoe below that I don't pay tax on. I also pay tax on accessories. What I want the query to return is this: Date | ShopperID | VATable Amount | Non VATable Amount| 2006-3-1 | 802135 | 146.95 | 54.00 | Basically each shopperID will only appear once which is why I am grouping the results (which I have managed). But I also need it to total up all the VATable amounts that it finds too for both the fields on the right above. I can get the date and order number without difficulty and I have managed to get the data like this so far where I have the same ShopperID where a shopper has bought more than 1 product: +------------+----------+-----------------+---------------------+ | Date | ShopperID| VATable Amount | Non VATable Amount | +------------+----------+------------+---------------+ | 2006-09-04 | 805284 | 0 | 64.00 | | 2006-09-04 | 805287 | 2.95 | 0 | <-- Here a | 2006-09-04 | 805287 | 3.25 | 0 | customer has | 2006-09-04 | 805287 | 3.45 | 0 | bought 4 products | 2006-09-04 | 805287 | 4.95 | 0 | - I need total | 2006-09-04 | 805327 | 0 | 53.95 | under each | 2006-09-04 | 805335 | 0 | 58.95 | ShopperID | 2006-09-04 | 805414 | 0 | 64.95 | | 2006-09-04 | 805414 | 3.25 | 0 | | 2006-09-04 | 805414 | 0 | 64.00 | | 2006-09-04 | 805414 | 0 | 69.00 | | 2006-09-04 | 805423 | 0 | 64.95 | | 2006-09-04 | 805423 | 0 | 69.00 | | 2006-09-04 | 805423 | 0 | 64.00 | | 2006-09-04 | 805423 | 3.25 | 0 | +------------+-----------+----------------+--------------------+ Heres the query currently getting these results (without the GROUP BY ShopperID). SELECT Date,orders.ShopperID, CASE WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?(UK)?( )?[^1-9][7-9]( )?(' THEN items_ordered.price -- If size 7 or above add price WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?(UK)?( )?[^1-9]10( )?(' THEN items_ordered.price -- If size 7 or above add price WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?(UK)?( )?[^1-9]11( )?(' THEN items_ordered.price -- If size 7 or above add price WHEN SUBSTRING_INDEX(Product,',',1) REGEXP 'Insole|Helmet|Laces|Wheels|Removal|Protection|Bag' THEN items_ordered.price -- If it is an accessory add VAT ELSE 0 END AS 'VATable Amount', CASE WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?( )?(Kids)?( )?( )?(UK)?( )?( )?(Kids)?( )?( )?[^1-9][1|2|3|4|5|6]( )?(' THEN items_ordered.price -- If size 1 - 6 add price to other column WHEN SUBSTRING_INDEX(Product,',',-1) REGEXP '( )?( )?(UK)?( )?( )?Kids( )?( )?(UK)?( )?12|13( )?( )?(' THEN items_ordered.price -- If size kids 12/13 add price to other column ELSE 0 END AS 'Non VATable Amount' FROM items_ordered,orders WHERE (items_ordered.ShopperID = orders.ShopperID) AND (Date >= ��-1-30') AND (Date <= ��-9-31');
View Replies !
Problem With Multiple Table Query Using Group By
I am running MySQL version 5.0.27-standard-log SELECT COUNT( rant_rants.* ) FROM rant_rants, rant_users GROUP BY rant_rants.userid LIMIT 0 , 15 Why won't this work? Foreach each rant_rants.userid I want the total number of rows in rant_rants with that userid. I have the second table in there because I am going to add more to this query once I figure this out. Any ideas?
View Replies !
How To Create Complex Query With Group By And Count
I have a table usertags: usertags ------------ - usertagid - usertagsiteid - usertagtext I want to select the usertagtext and count of usertagtext grouped by usertagtext. I only want it to show usertagtext that also have a match in the usertagsiteid to a given value for usertagtext. Not sure if that makes sense so here is an example: ......
View Replies !
Count And Group By Month In 1 Query For A Date Range?
I have a table that has employees work history on it. Basically the only thing I am worried about is the start and end date. My boss wants a report that charts out the quantity of workers for the past 12 months group by the month. For example Employee--Start Date--End Date Charles-----2008-6-3---2009-2-1 Vicky-------2009-1-1---Present So in this case there would be a quantity of 1 from June 08 to December 08. Then their would be a quantity of 2 from Jan 09 to Feb 09. And Vicky would also be counted for March since she is still working. I can do this by creating PHP functions and putting it all together, but I was just wondering if there was a way to do this all in 1 query.
View Replies !
Full Yearly Weekly Group By Group Report
I have table called tblTransactions: TblTransactions Group | Months | Year | Transaction A | 3 | 2007 | 45 B | 2 | 2007 | 89 A | 7 | 2007 | 50 A | 8 | 2007 | 34 B | 4 | 2007 | 55 A |12 | 2007 | 10 A | 1 | 2008 | 88 B | 3 | 2008 | 34 B | 5 | 2008 | 70 A | 5 | 2008 | 45 A | 9 | 2008 | 88 B | 7 | 2008 | 99 A |10 | 2008 | 77 A |11 | 2008 | 99 How is the T-SQL to make the result like this (the result will display all of the week (FULL), the target is I can compare week by week between years and group. A | 1 | 2007 | 0 B | 1 | 2007 | 0 A | 2 | 2007 | 0 B | 2 | 2007 | 89 A | 3 | 2007 | 45 B | 3 | 2007 | 0 A | 4 | 2007 | 0 B | 4 | 2007 | 55 A | 5 | 2007 | 0 B | 5 | 2007 | 0 A | 6 | 2007 | 0 B | 6 | 2007 | 0 A | 7 | 2007 | 50 B | 7 | 2007 | 0 A | 8 | 2007 | 34 B | 8 | 2007 | 0 A | 9 | 2007 | 0 B | 9 | 2007 | 0 A | 10| 2007 | 0 B | 10| 2007 | 0 A | 11| 2007 | 0 B | 11| 2007 | 0 A | 12| 2007 | 10 B | 12| 2007 | 0 A | 1 | 2008 | 88 B | 1 | 2008 | 0 A | 2 | 2008 | 0 B | 2 | 2008 | 0 A | 3 | 2008 | 0 B | 3 | 2008 | 34 A | 4 | 2008 | 0 B | 4 | 2008 | 0 A | 5 | 2008 | 45 B | 5 | 2008 | 70 A | 6 | 2008 | 0 B | 6 | 2008 | 0 A | 7 | 2008 | 0 B | 7 | 2008 | 99 A | 8 | 2008 | 0 B | 8 | 2008 | 0 A | 9 | 2008 | 88 B | 9 | 2008 | 0 A |10 | 2008 | 77 B |10 | 2008 | 0 A |11 | 2008 | 99 B |11 | 2008 | 0 A |12 | 2008 | 0 B |12 | 2008 | 0
View Replies !
Group By With Order BY, Or INSERT INTO (SELECT * GROUP BY)?
I seem to have a few duplicates entries in a very large database. My database has two identical tables. One is written to on a regular basis (every few seconds) and the other is used for queries, and updated from the write table on a daily basis. I'm trying to get rid of duplicate entries for the queries, and I thought the most effective way to do this would be to run the query as Expand|Select|Wrap|Line Numbers
View Replies !
Invalid Use Of Group Function (was "Baffled By Query Error")
trying to figure out why I keep getting this error with the following query: SELECT c.account_id,a.name,a.company,SUM(c.agent_product_time) AS mins FROM account a LEFT JOIN calls c ON c.account_id = a.id WHERE c.calldate >= DATE_SUB(NOW(),INTERVAL 14 DAY) AND c.agent_id = 2 AND SUM(c.agent_product_time) >= '500' GROUP BY c.account_id ORDER BY mins ERROR: #1111 - Invalid use of group function
View Replies !
GROUP BY With Order Inside Group
I have a problem when grouping records - I can't manipulate data inside group. For example, I have table `images` with fields `name` (name of image, not unique) and `dtadded` (date of image adding). Then, I need to get all images names with distinct names where each name must be latest added name. Sample: ------------------------------ id, name, dtadded ------------------------------ 1 name1 2007-10-15 00:00:00 2 name2 2007-10-15 00:00:00 3 name1 2007-10-16 00:00:00 ------------------------------ I need to receive 2 results (for each name) 3 - name1 - 2007-10-16 00:00:00 2 - name2 - 2007-10-15 00:00:00 If I use SQL code: SELECT images.id, images.dtadded, images.name, count(name) FROM images GROUP BY `name` ORDER BY dtadded DESC I get results 1 - name1 - 2007-10-15 00:00:00 2 - name2 - 2007-10-15 00:00:00 It groups records with first row in database, but I need last row in table for each name. Question: How can I order results in side group to get needed results as described above?
View Replies !
Top Record From Each Group In GROUP BY
I never could figure out a good way to do this. I am doing a group by statement, and want the top record from each group to be returned only. My query is to find the current win/loss streak for each team. Here is the sql: SELECT id, team_name, result, MIN(date) AS startdate, MAX(date) AS enddate, COUNT(*) AS games FROM ( SELECT t.id, t.team_name, sched.date, CASE sched.draw WHEN 1 THEN 'draw' ELSE CASE sched.winner_id WHEN t.id THEN 'win' ELSE 'loss' END END AS result, ( SELECT COUNT(*) FROM fantasy_schedule sched2 WHERE ( CASE sched2.draw WHEN 1 THEN 'draw' ELSE CASE sched2.winner_id WHEN t.id THEN 'win' ELSE 'loss' END END ) <> result AND sched2.date >= ��-01-01' AND sched2.date < ��-01-01' AND sched2.date < sched.date AND sched2.fantasy_league_id = 6 AND (sched2.fantasy_team1_id = t.id OR sched2.fantasy_team2_id = t.id) ) AS rungroup FROM fantasy_schedule sched JOIN fantasy_team t ON t.id = sched.fantasy_team1_id OR t.id = sched.fantasy_team2_id WHERE sched.last_update < NOW() AND sched.date >= ��-01-01' AND sched.date < ��-01-01' AND sched.fantasy_league_id = 6 ) bighonker GROUP BY id, team_name, result, rungroup ORDER BY enddate DESC And a subset of what is returned: +----+----------------------------+--------+------------+------------+-------+ | id | team_name | result | startdate | enddate | games | +----+----------------------------+--------+------------+------------+-------+ | 1 | The Macho King | loss | 2005-12-17 | 2005-12-17 | 1 | | 1 | The Macho King | win | 2005-12-11 | 2005-12-11 | 1 | | 1 | The Macho King | loss | 2005-11-24 | 2005-12-04 | 2 | | 1 | The Macho King | win | 2005-11-20 | 2005-11-20 | 1 | | 1 | The Macho King | loss | 2005-11-13 | 2005-11-13 | 1 | | 1 | The Macho King | win | 2005-10-30 | 2005-11-06 | 2 | | 1 | The Macho King | loss | 2005-10-16 | 2005-10-23 | 2 | | 1 | The Macho King | win | 2005-10-09 | 2005-10-09 | 1 | | 1 | The Macho King | loss | 2005-10-02 | 2005-10-02 | 1 | | 1 | The Macho King | win | 2005-09-08 | 2005-09-25 | 3 | | 2 | General Grievous | loss | 2005-12-17 | 2005-12-17 | 1 | | 2 | General Grievous | win | 2005-12-11 | 2005-12-11 | 1 | | 2 | General Grievous | loss | 2005-11-24 | 2005-12-04 | 2 | | 2 | General Grievous | win | 2005-11-13 | 2005-11-20 | 2 | | 2 | General Grievous | loss | 2005-11-06 | 2005-11-06 | 1 | | 2 | General Grievous | win | 2005-10-23 | 2005-10-30 | 2 | | 2 | General Grievous | loss | 2005-10-16 | 2005-10-16 | 1 | | 2 | General Grievous | win | 2005-10-02 | 2005-10-09 | 2 | | 2 | General Grievous | loss | 2005-09-08 | 2005-09-25 | 3 | | 3 | Ultimate Jarin | win | 2005-11-06 | 2005-12-24 | 8 | | 3 | Ultimate Jarin | loss | 2005-10-23 | 2005-10-30 | 2 | | 3 | Ultimate Jarin | win | 2005-10-09 | 2005-10-16 | 2 | | 3 | Ultimate Jarin | loss | 2005-09-25 | 2005-10-02 | 2 | | 3 | Ultimate Jarin | win | 2005-09-08 | 2005-09-18 | 2 | | 4 | The Hulkamaniacs | loss | 2005-12-11 | 2005-12-11 | 1 | | 4 | The Hulkamaniacs | win | 2005-12-04 | 2005-12-04 | 1 | | 4 | The Hulkamaniacs | loss | 2005-11-24 | 2005-11-24 | 1 | | 4 | The Hulkamaniacs | win | 2005-11-06 | 2005-11-20 | 3 | | 4 | The Hulkamaniacs | loss | 2005-10-30 | 2005-10-30 | 1 | | 4 | The Hulkamaniacs | win | 2005-10-09 | 2005-10-23 | 3 | | 4 | The Hulkamaniacs | loss | 2005-09-18 | 2005-10-02 | 3 | | 4 | The Hulkamaniacs | win | 2005-09-08 | 2005-09-08 | 1 | | 5 | WHO WANTS TO KISS HOMER? | win | 2005-12-04 | 2005-12-04 | 1 | | 5 | WHO WANTS TO KISS HOMER? | loss | 2005-11-20 | 2005-11-24 | 2 | | 5 | WHO WANTS TO KISS HOMER? | win | 2005-11-13 | 2005-11-13 | 1 | The top record in each group is the current streak. I want only that top record for each teamr returned. I could simply 'order by enddate DESC' and in my php script loop until I have one record per team, but is there any way I can only return the top result for each team in the query itself? Here is the corresponding table schema: +-------------------+---------------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------------+---------------------+------+-----+---------------------+----------------+ | id | int(10) unsigned | | PRI | NULL | auto_increment | | fantasy_league_id | int(10) unsigned | | MUL | 0 | | | week_num | tinyint(3) unsigned | | MUL | 0 | | | fantasy_team1_id | int(10) unsigned | | MUL | 0 | | | fantasy_team2_id | int(10) unsigned | | MUL | 0 | | | winner_id | int(10) unsigned | YES | | 0 | | | date | date | | | 0000-00-00 | | | last_update | datetime | | | 0000-00-00 00:00:00 | | | draw | tinyint(3) unsigned | | | 0 | | +-------------------+---------------------+------+-----+---------------------+----------------+ fantasy_team +---------------------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------------------+-----------------------+------+-----+---------+----------------+ | id | int(10) unsigned | | PRI | NULL | auto_increment | | fantasy_league_id | int(10) unsigned | | MUL | 0 | | | user_id | mediumint(8) unsigned | | MUL | 0 | | | team_name | char(40) | | | | | | fantasy_division_id | int(10) unsigned | YES | MUL | NULL | +---------------------+-----------------------+------+-----+---------+----------------+
View Replies !
How To Log Specific Queries
I've got this DB. Every few minutes new data comes in and is inserted. Now and then for some reason duplicate data comes through - not a big problem - unique key. However I'd like to log the insert failures, due to dupes, but I don't want to log all of the good inserts also.
View Replies !
Specific Table
Hello MySQL gurus, This is the command : mysqldump -u$user -p$pass $database >$backup Can you exclude a specific table, say table UnNecessaryAndHugeTable, from the dumping process? that is mysqldump all tables except table UnNecessaryAndHugeTable
View Replies !
Changing The Value In A Specific Row
i have a forum that i am trying to add a username kind of thing to. what i have done so far is to set up a table that has three coloumns in it, USERNAME, PASSWORD, and STAT, of which stat is a boolean value that i use to determine if they are logged in or not (what i intend for it to be), and the other two are pretty self explanatory. my question is is there a way for me to get into this specific row and change the value stored inside STAT, or is my only choice to completely delete this row then re-enter it into the table everytime stat has to change? thanks guys. btw good forum you have here
View Replies !
Copy Specific Row
I have a database that host a "user login data," and i have built a new script to expanse the service in other computer's database. I want to know are there any methods to copy the user data row from the "user login data" to the new computer's database that i have just built? I am not talking doing this manually by hand, but doing this like replication a specific of row.
View Replies !
Getting Specific Results
my db looks like this: username - skill1 - skill2 - skill3 John - warrior - thief - warrior How can i only output the fields that are warrior? Ive tried this but it dident work: SELECT skillsM1, skillsM2, skillsM3 FROM ugd HAVING skillsM1 AND skillsM2 AND skillsM3 = 'warrior' Or is it necesary for me to change my db structure?
View Replies !
Specific Data
Ive been struggling with this for some time, wonder if someone can help, I have a database table column that has some information within it that I do not want, however there are over 100,000 records and it'll take some time to update them all - I'm trying to find the sql command to update the info currently I have: PHP Code: REPLACE * from table WHERE `column` LIKE '%data_not_needed%' I want to replace it with nothing so it simply takes the not require word / data out from that column - what else can I add into that sql command to show this?
View Replies !
Specific Rows
When I was creating a database I misassigned some rows and now I just want to change those 4 rows but can't figure out how to do it. I tried using this command Code: update variables set bl_id=16 where bl_id in (SELECT bl_id from variables where var_id between 379 and 382)
View Replies !
Specific Search
I'm creating a PHP application in which I need to do a specific search that I'm not being able to formulate it. On my database, I have a table that register different organizations. Each organization has a primary key called org_id, which is an integer auto_increment. Well let's say I want to create a search in which I want only specific organizations to show up. Let's say I have 100 organizations registered, and I want to search for organizations with org_id 1, 4, 7, 15, 33, 45 and 85. Well, how do I write a MySQL code that will return these organizations, only these organizations, nothing else?
View Replies !
Specific Join
I have a table with roughly 50 million records that contains user data for my company's sites. Two of the columns in the table are varchar columns that show the url a user is coming from (referurl) and the url they're going to (url). So if you look at user's session, you can see the progression they made while navigating through our site. Each user's session is identified by a number, which is indexed. I recently created a companion table that assigns a number to both the url (url_id) and the referring url (ref_id) for each one of the records in the main table (a table matching those two values is elsewhere in the database. These two tables have the same auto-incrementing unique id number (uid), and the same number of records. The two non-unique numerical fields in the companion table have a multiple index on them. Previously, I have been using the mysqldb module for python to do a very particular kind of search. Basically what I want to do is search for every record matching a url that I input, then find the url of the first site that user came in on. What I would do is SELECT the records that matched my search (I was matching them on the url field I mentioned above), then have python submit a query to get the sessions from each one of those results. Python would then find the url from those results that had the highest id number and matched one of our 15 or so landing pages in the url field (which would make it the most recent landing that that user made to our site). It always seemed to me that this is something that could be accomplished more quickly by a more complicated MySQL query that uses the newly created companion table instead of matching the string of the url. I've come up with this query to just get the results I want using the companion table (table names changed to protect the innocent): ......
View Replies !
Specific Date
My registration_date column has to be a DATETIME type and I have to zero in on a specific date. SELECT registration_date FROM reserved WHERE ( registration_date >= '2005-18-06 00:00:00' ) AND ( registration_date <= '2005-18-06 23:59:59' ) Is there a more streamlined way I can extract just the date from a DATETIME type, or is this the best or only approach
View Replies !
Updating A Specific Row
I have a question about updating a row in a table. This table keeps a record of when a user logs in/out with username, time of login/logout and ip-adress. When a user logs in, the logouttime is of course left blank. When that user logs out I need to update that row with the logout time. I know I need to use "UPDATE log SET timeLoggedOut = '$timeLoggedOut'" I have a session variable with the identity of the user currently logged in. But how do I update only the last row in the table containing the username of the user logging out? If I only use WHERE username='$userloggedin' it will update every row containing that username..... EDIT: I guess I could use $sql = "SELECT MAX(loginID) from log WHERE username = '$userloggedin'" to identify the row I need to change, right? But how do I return that information from the db so that I can put it in a php variable?
View Replies !
Retrieving Specific Records
I am trying to establish (using a straight MySQL query in version 4.0.18) the level of qualifications someone has, i.e. degree, postgraduate degree or other. To simplify things, I need to use only two tables, namely: master table, containing: staffno (Primary Key); forename; surname; psnquals table, containing: psnqualid (Primary Key); staffno (Foreign Key); levelid (where 1 = degree, 2 = postgraduate degree, 3 = other) In the followning query, staffno 1 has three qualification levels, 1, 2, 3. The query finds the first matching record (where cdelevelid = 1) and then stops, making it appear as if the employee had no postgraduate qualification. One person can have one or all of the above qualifications. I need to write a query that shows if a person has one, two or all of the above. I tried using the following query, but it seems only to look at the first psnqualid record it finds and then stops: SELECT m1.staffno, m1.forename, m1.surname, CASE WHEN p1.cdelevelid = 1 THEN "D" WHEN p1.cdelevelid = 2 THEN "P" ELSE "O" FROM `master` m1 INNER JOIN psnquals p1 ON m1.staffno = p1.staffno I also tried using an IF statement, but this also seemed to stop after finding the first matching record in the psnquals table.
View Replies !
Privileges On Specific Fields In A DB
How do I make a table with fields again, say, name, age, address, wife etc. that I want people to be able to look at (with any client like MysqlCC etc). but only be able to modify certain fields, eg. wife (this includes deleting the field from the database, maybe) - the name, age etc. must be left intact, ie. not changeable by anyone.
View Replies !
Order By Specific Values
Is it possible to order by specific values in a column. For example a column may contain values like N, N1, N2, S, S1, S2, R, T. I want to order by in such a way that all the records with N, N1, or N2 in that specific column will show up on the top. All the records with S1, S2 values will show up next and finally all records with R and T values in that specific column will show up. Please give me the select syntax.
View Replies !
|
TRACKER
