Counting Elements
I got sql query like this that worked fine on a server, then a moved my site to another server and it doesn't work.
UPDATE user
SET on_time = (SELECT on_time
FROM review
WHERE userID = '$userID'
GROUP BY on_time
ORDER BY COUNT (*) DESC
LIMIT 1)
WHERE userID = '$userID'");
I found that ORDER BY COUNT (*) is the problem but don't know how to get the most popular on_time value to update the user table. The query below doesn't work because I need to get only the on_time value, not on_time + count.
UPDATE user
SET on_time = (SELECT on_time, COUNT( * ) AS count
FROM review
WHERE userID = '$userID'
GROUP BY on_time
ORDER BY count DESC
LIMIT 1)
WHERE userID = '$userID'");
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Counting Elements Of A Tree-like Structure
I have a table (tblRows) that holds a tree structure of rows by using a parentid and sortorder like this: id___parent___sortorder_______________resulting_tree____________index ______________________________________root 14____0________0______________________|-14________________________1 15____0________1______________________|-15________________________2 16____15_______0______________________|__|-16_____________________3 17____15_______1______________________|__|-17_____________________4 18____0________2______________________|-18________________________5 19____0________3______________________|-19________________________6 20____19_______0______________________|__|-20_____________________7 21____20_______0______________________|____|-21___________________8 22____0________4______________________|-22________________________9 at some point I would like to know the "row index" of a specific row without having to select all rows and counting. However, I can't figure out the SQL that does so - can anybody help me ? I suppose I would have to select "all items placed above in the tree" and then just count these, but I cant see how to select that. I think I could do it with nested selects, but thats not an option as the system is currently running on a mysql 4.0 server.
Grouping Elements
I was wondering if it is possible to group linked elements using SQL directly. For example, if I have some table like: Column A | Column B | Column C | Column D 1 | 2 | A11 | A32 2 | 3 | A13 | A22 3 | 4 | A14 | A23 5 | 6 | A19 | A24 7 | 8 | A18 | A25 1 | 9 | A17 | A26 I want to group all linked items together. So in the above case my output would look something like: Table 1: 1 2 3 4 9 Table 2: 5 6 Table 3: 7 8 or maybe something like with the above one still preferred: 1,2,3,4,9 5,6 7,8 extracted into another table... One other question I had was, can a database have a million tables? I mean, if I had many such groups, would it be efficient to create a table for each group or is there any other approach?
Distinct Elements
I have two tables. One contains a list of people, the other contains a list of things people do. I store the list of things that people do by the people id number. When I create a list of people to work on I only want to select those people from the people table that do not appear in the things that people do table. Here is the query I am using: SELECT DISTINCT LOWER(P.txtFirstNames) AS txtFirstNames, LOWER(P.txtLastName) AS txtLastName, P.nId, P.txtAptNum, P.txtHouseNum, LOWER(P.txtStreetName) AS txtStreetName, LOWER(ST.txtStreetType) as txtStreetType, P.txtStreetNESW, LOWER(CL.txtCityName) as txtCityName, PV.nId, PL.txtProvAbbrv, P.txtPostalCode, P.txtPhoneDay FROM person AS P LEFT JOIN persvolunteer as PV ON P.nId = PV.nId LEFT JOIN citylist as CL ON P.nCityNum = CL.nCityNum LEFT JOIN streettypes as ST ON P.nStreetTypeNum = ST.nStreetTypeNum LEFT JOIN provincelist as PL ON P.nProvinceNum = PL.nProvNum WHERE P.txtArea = '021' ORDER BY P.txtHouseNum, P.txtStreetName, ST.txtStreetType;
Getting A Count Of Distinct Elements
If I have a table like this: +----+---------------+-------------+ | id | A | B | +----+---------------+-------------+ | 1 | 1 | -1040187392 | | 2 | 1 | -1040187392 | | 3 | 2 | 1409286144 | | 4 | 2 | 1409286144 | | 5 | 2 | 1409286144 | | 6 | 2 | 1442840576 | | 7 | 3 | 989855744 | | 8 | 3 | 2080374784 | | 9 | 3 | 989855744 | | 10 | 4 | -671088640 | | 11 | 4 | 1107296256 | | 12 | 4 | 1107296256 | | 13 | 4 | 1073741824 | | 14 | 4 | 1073741824 | | 15 | 4 | 1107296256 | +----+---------------+-------------+ I want to get a frequency count of the elements. Can I do it with mysql? I mean, I want something like the following: +---------------+-------------+---------+ | A | B |frequency| +---------------+-------------+---------+ | 1 | -1040187392 | 2| | 2 | 1409286144 | 3| | 2 | 1442840576 | 1| +---------------+-------------+---------+
Ordering In Queries By Sequence Of Elements In "IN"
I need a query which delivers results in the order I have in the element sequence after "IN". SELECT aid, cid, vid FROM table WHERE aid IN (5,7,3) I want to see the resuts of the query submitted in the same order, it means the first row should be with aid 5, the second with aid 7 and the third with 3. Without "ORDER BY" I get the results in sequence 3, 5, 7.
Sub Counting For Each Row
For each row in my table, I'm getting stuck doing a count. An extract of the table is as follows: user_id | name | favourite_user 1 | "person 1" | 2 2 | "person 2" | 1 3 | "person 3" | 1 4 | "person 4" | 2 5 | "person 5" | 2 I'm trying to select all rows from the table, and at the same time, a count of how popular each person is. In two queries, this can be achieved by: SELECT * FROM TABLE; SELECT favourite_user, COUNT(*) FROM TABLE GROUP BY favourite_user How can I combine the queries so that for each person I return their data and a count of their popularity?
Counting Records
Got a question for you that is really stumping me. I have 3 tables job_jobs ------------ job_id job_name . . . job_cats ------------- cat_id cat_name . . . job_jobsToCats ------------- jtc_id job_id cat_id *************** ok i have those 3 tables to categorise the jobs on the site and im trying to build a query that will essentially get all categry names and display the number of jobs in that category so when i output it i will get accounting (12) admin(2) etc etc
Subquery And Counting
I have a table called people in my database. it looks like this +----+------+ | id | name | +----+------+ | 2 | bob | | 1 | Jill | | 3 | Jill | | 4 | John | | 5 | Jill | +----+------+ I want to display the distinct name and how many times they are in the table So like this Jill - 3 bob - 1 John - 1 How would I go about doing this. I thought maybe something like this but not to sure [code] SELECT COUNT(name) FROM people WHERE name = (SELECT DISTINCT name FROM people); [code]
Counting Like A Human
I have a query that does an ORDER BY area ASC. When it does the order by it is ordering by how a computer counts rather than how people count. As an example here is some of my data ....
Counting Query
Is there a MySQL function that i can use to just count the number of rows in a table instead of listing all the IDs and running a command on the result?
Counting Rows
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home2/itsyouma/public_html/staff/memberlist.php on line 6 <?php $SQL_statement = "SELECT * FROM users"; $resultset = mysql_query($SQL_statement); $count_rows = mysql_num_rows($resultset); ?>
Counting Rows
I have a table containing Article | State ------------------- 1 | 10 2 | 10 1 | 5 1 | 5 and I want to count all Articles in one row with the different states SELECT Article, COUNT(State=10) AS Ordered, COUNT(State=5) AS Free FROM...... GROUP.... but this SQL doesn't work (on my Server 4.0.20) I don't know if it work above but in the docu I can read somthing COUNT(expr) my be it works in 4.1 but is there another way that works wit 4.0.20?
Hour Counting
I keep in a ltable 'log' the time and date each user loged-in, and the time and tahe each user loged-out... among other things. What I want to do is a report to show how long each user was loged in the system. I'm using MySQL 4.0.15. I was thinking if I could do it with a single query but I cant figure out how. Table log: ....
Counting Query
I need a slect statement to return the count of rof rows if a particular field matches a criteria similar to the COUNTIF function in Excel. We are using mySQL 3.23.58. Is there a way to do this and if so how?
Counting Days
How can I make a query such that in the "WHERE" part of my clause, I want to put something to the effect that, say, X days have elapsed since a given date. For example, suppose in my table, I have a boolean field and a date field. I want to create a query asking for those rows where X days have elapsed from the date field in a row and the boolean, say, is false. Do I need to create a third field, called, say "X" for how many days have elapsed between the date field and today, and update every row in the table every day?
Counting Function
I have a table with a list of names and some are duplicate and I would like to count them?? It's like this: Drew Simon Alli Tim Beth Alli Simon Drew Simon Simon Alli Then to export: Drew - 2 Simon - 4 Alli - 3 Tim - 1 Beth - 1 How could I go about asking this query? SELECT COUNT(name) FROM table ??
Counting Correctly!
I want to ORDER BY on a column. Straightforward until I discovered that MySQL doesn't count the same way I do. My values are TA1 TA2 TA3 TA4 TA5 TA6 TA7 TA8 TA9 TA10 TA11 TA12 TA13 TA14 When I ORDER BY, MySQL thinks TA10 is less than TA2, Resulting in TA1 TA10 TA11 TA12 TA13 TA14 TA2 TA3 TA4 TA5 TA6 TA7 TA8 TA9 I know a simple solution would be to rename my codes TA01, TA02 etc, but I can't as the values are an external code that we have to use. Is there a way I can get MySQL to ORDER BY correctly on these values?
Counting Unique
I have the following code... $query = "SELECT DISTINCT newsid FROM comments"; $result = mysql_query($query); while($row = mysql_fetch_array($result)){ which works fine. My questions is there any way to determine which newsid shows the most amount of times. I'm trying to make a "top5" sort of things and it would make it much easier.
Virtuel-Counting-Row
I use the database mysql v.4. My problem is... I have a select like: select * from user where language = "de"; the result are then: id name =================== 1 max 5 tim 99 otto so and what i need is a virtuell row (pos) with a counting index of the result: id name pos =================== === 1 max 1 5 tim 2 99 otto 3 how can i make this with mysql? My first idea was to made that with views but mysql can't do that!
Counting An Item
I was wondering if someone could tell the correct way to go about using the count statement within a select query. I want to count students attempts at taking an exam but only show users who have done it more than 3 times... Table: Student_Attempts Stu_Name Attempts Bob 3 Mary 6 Sam 8 Jane 1 Its the more than 3 times part that I'm stuck on.
Counting Usage Per Day
I want to record how many times an application is used in a day. Is it possible to create a table with a date field that automatically creates a new record each day with the present date as the primary key.
Counting With LIMIT
I'm trying to retrieve the number of records found using an offset (with LIMIT), however I get no results back eg.: SELECT count(propID) FROM properties WHERE propAvailable=-1 AND propType='s' LIMIT 10,10 Produces: MySQL returned an empty result set (i.e. zero rows) However, removing the LIMIT eg: SELECT count(propID) FROM properties WHERE propAvailable=-1 AND propType='s' Produces: count(propID) 38 I don't, though, want a count of all the records. The LIMIT works without the count eg.: SELECT propID FROM properties WHERE propAvailable=-1 AND propType='s' LIMIT 10,10 Produces Showing rows 0 - 9 (eg. 10 records) But I only want the count, not the rows - arggghh! How can I get back a count of a subset of the data? Is there another way of syntaxing this query?
Counting Records
Ive tested the following in PHPMYADIM and it returns each record and how many times it occurrs. CODESELECT gm, COUNT(*) FROM league_ws GROUP BY gm ORDER BY 'COUNT(*)' DESC
Counting Set Items
Is it possible to count a set of items? Example count("2,3,4,5",","); Would return 4 Count items based on the delimeter? Or just count set items.
Counting Occurrences
I have 2 columns: 'keyword' and 'Category'. For example: keyword | category -------------------------- shoe | clothes shoe | sport shoe | clothes Now, I want to create this output table: keyword | clothesFrequency | sportFrequency ------------------------------------------------------- shoe | 2 | 1 Because shoe occurs twice in the category clothes and once as the category sport. I am looking for an efficient method, because in my final application I will use 5 different categories and millions of keywords.
Counting Tables
i've been through this and a few forums and the docs, and don't seem to have come across a way of counting how many tables are in a database. specifically while using a like 'sometable%' would be useful for me is this possible? if there is not a command for this, i see that mysql>show tables like 'sometable%'; returns a table count at the bottom, maybe i can retrieve that somehow?
Counting Problem
I need your help here, I've been trying to solve this for hours and it's driving me crazy. It's yet another counting problem. Let's say I have a "friends" table looking like this: +-------------+----------------+--------+ | Town | Name | Eyes | +-------------+----------------+--------+ | Paris | Nicolas | blue | | Paris | Claire | blue | | Paris | Simon | brown | | Paris | Marie | black | | NY | Jason | blue | | NY | Frank | green | | NY | Amy | blue | +-------------+----------------+--------+ What I want is to know the number of friends with a particular eye color living in each town, like this: +------------+------+-------+-------+-------+ | Town | blue | brown | green | black | +------------+------+-------+-------+-------+ | NY | 2 | 0 | 1 | 0 | | Paris | 2 | 1 | 0 | 1 | +------------+------+-------+-------+-------+ I've always been very bad in mastering JOINs, and here I'm at a complete loss. Is there a charitable soul out there that can show me how to solve this?
Counting Distinct
I have a field that contains filenames. I'd like to count how many times a certain file name appears in that field. So, for example if I have 3 unique filenames, I would like to be able to see something like this: file1 = 120 file2 = 109 file3 = 76 The first part seems should be: select count(file) from download But how to specify returning a count for each distinct file?
Counting Rows
both of these return the same error. "Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource" $checkip = "SELECT * FROM 'vote' WHERE ip='$user_ip', vote_id='$vote_id'"; $ipcheck=mysql_query($checkip); $total=mysql_num_rows($ipcheck); $checkemail = "SELECT * FROM 'vote' WHERE email='$email', vote_id='$vote_id'"; $emailcheck=mysql_query($checkemail); $total2=mysql_num_rows($emailcheck);
Counting Total Row
I am trying to select the total row count from two different tables. I am getting a result back but it is returning the same count for both even though they have different amounts. This is what I have so far:- SELECT COUNT(companies.comp_id) as tcustomers, COUNT(project_id) as tprojects FROM companies, projects as you can see I have two tables, companies and projects.
Counting The Number
I'm having difficulty figuring out how count the number of each distinct value in a column.Say I have a column called 'score' that contains a score value of 1-4, how would I could how many 1s, 2s, 3s and 4s there were in this column?
Counting Rows
In my MYISAM table I have an index (Index_A) on 2 fields (Field_A, Field_B). There are millions of rows in the table. The cardinality of Index_A is 53. I think a query to count the number of rows that match a pair of values for Field_A and Field_B should execute blindingly fast (in the index count the number of leaf nodes for the B-Tree node representing the indexed pair of values). My query looks like: SELECT count(*) FROM `table_A` WHERE `Field_A`=5 and `Field_B`=1 Why does it take 20 seconds for this query? And how can it be made faster?
Counting Occurrences
I have a table Users which contains the id and vote1, vote2....vote10. The data stored in each vote field is the id of a product they are voting for as their favourite 10 products out of lots of different products. are there any functions in sql that allow me to count the occurrences of product ids in all vote1 and then vote2 and so on? With thousands of products I dont want to have to count for each one, only the ones that have been voted for.
Counting Sub Categories
It is setup like this: catID - PK catName - varchar parentID - integer For example: Accomodation (catID = 1) is a top level category (parentID = 0). Shared Houses is a sub category of accomodation (parentID = 1) Hotels is a sub category of accomodation (parentID = 1) Say I have 2 records in shared houses and 1 in hotels, how would I count it so Accomodation has a count value of 3?
Counting Records
I am trying to figure out how to count a subset of the records returned from a query. I have a query that returns records that fit the criteria events_registrants.pgApproval='1' AND events_registrants.approval='1'. Out of those records, I need to COUNT(events_registrants.pgApproval) which works fine and I also need to count a subset of those records which also fit the additional criteria events_registrants.complete=1. The code below doesn't return the correct count for events_registrants.complete=1. It gives me the total count of all instances of events_registrants.complete (which includes those that are equal to 0) so how would I write.
Counting Clicks
I want to create a click counter. When ever a user clicks a link the counter updates. I am using PHP for server side scripting and mySQL as backend database. If somebody could tell me about any mySQL database level procedure/trigger or PHP script or any CGI or pearl script for counting clicks,
Counting Records
I have three tables I am trying to do a count on to give me a total amount. The problem is that somes of the sales order numbers are unique to the other tables but I want to count the unique records as well as the records that are not in the other tables. Is there an easy way to do this?
Counting By Age/group
how would i go about doing something like getting ages from database and then grouping them into age group? eg 18-20, 20-24 etc.. I could do that using PHP (i think, i have logic thought out) but was wondering if it would be easier to do in mysql
Counting Results
i'm trying to find a way of counting the results in a mysql table. Say I have the fields CAR_MAKE | CAR_MODEL | COLOUR | Is there are way of saying car_make=bmw car_model=z3 colour=black how many of these are there im my table
Counting Rows
I have a value '120' standing in column 'number' from a table called 'example'. Let's say I want to know how many records have been before this 120..how should I 'count' that? Exampledatabase 'example': ---------------- |Number| Word | ---------------- |13 | this| |22 | is| |120 | an | |422 | example| ---------------- In this example my return should be 2(the 13 and 22 are the records counted).
Counting The Records
is it possible to do this in one statement: I wish to order by a certain field and count the records before a specific record, so as to get a sort of ranking.
Counting Totals
I have a table of messages. Each message has a me_date datetime. I want to get a count of the number of messages every day in the last 30 days - even the days when there were none. How can I do this in a single statement? so far I have: select count(me_id) as a, to_days(me_date) from messages where to_days(me_date)>to_days(now())-90 group by to_days(me_date) but this doesn't include the 'zero' days (days when there were zero messages).
Counting Records
I have a downloads system on my website, and for each download I make an entry into a log database, which contains the userid of the person who downloaded it, the fileid, and the date it was downloaded (stored in unix timestamp). Now, I want to make a stats page, that shows how many file are downloaded each day. How do I group this query? I can't just do it normally via the date field because the value in each record will be different even if they are on the same day. So, basically I want to count all records grouped by each day, although the date field on each record will be different. How do I do this?
Counting From Different Tables
I have a problem getting counts from 2 different tables, via joins from the parent table. At the moment, I am doing this: CODESELECT q.*, q.id as q_id, t.town, c.category, count(qa.id) AS answers FROM qq_local q LEFT JOIN qa_local qa ON q.id = qa.q_id AND qa.active = 1 LEFT JOIN towns t ON t.id = q.town_id LEFT JOIN q_category c ON q.category_id = c.id GROUP BY q.q ORDER BY q.q_id DESC
Counting Rows
I have an sql table (messages) that holds messages related to conversation names. I need help to formulate a query that extracts the number of unviewed messages for each distinct conversation. For example, with the following 4 tuples: cname viewed weather viewed weather unviewed mobiles unviewed weather unviewed I would need the output as: You have 2 unviewed messages in weather and 1 unviewed message in mobiles
Counting Fields
I need to retrieve the number of occurences of each distinct field in a specific column. This is what I am doing right now: Code: SELECT DISTINCT referrer FROM counter and for each record that that query returns: Code: SELECT COUNT(*) AS count FROM counter WHERE referrer = '".mysql_real_escape_string( $row[ 'referrer' ] )."' (using PHP) is there a way to make a single SQL query that will count the occurences of each distinct field of a specified column?
The Absolute Best Way Of Counting Rows
I've written a pagination class in PHP that basically gets a query, and returns, let's say, the first 50 results, then prints a navigation bar with links to the pages (1,2,3 etc) For that class, I need: 1- to know the total results from the query 2- to run a query with "limit 50" Up to now I have been using mysql_num_rows($query) to count the number of rows and then running the "LIMITed" query, but to me this seems a waste of resources Isn't there a better alternative for counting rows? I've tried using COUNT(*) but the script receives some really complex queries (sometimes using 3 tables) and sometimes returns "1" as the total number of rows. I can't find a way to "standardize" it in order to let me feed it whatever query I want What I mean is, I need a fool-proof way to feed it ANY query, and it will return me the number of rows
|
TRACKER
