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).
View Complete Forum Thread with Replies
Related Forum Messages:
Totals/Mean Values
I assume this is a very simple question, I just don't know the answer! I am planning on setting a MySQL database for some real estate property that has already been sold. The user will enter the information for each parcel sold for homes, land, etc. What I want to do is: a) Total the price columns for each and take the average price - have all of this calculate automatically b) Also call on these total values from an intro page displaying the totals for each year First, is this possible? Second, how would I go about doing this? Do I need to add extra fields to the table for these totals, or are there MySQL commands to do the arithmetic
View Replies !
Indexed Totals
don't know if that subject is correctly put, but here's what i'm trying to accomplish: I want to be able to tell how many rows in a given table, and for a given INDEXED column, carry any given ID. Example: suppose I have a field named 'IDNumeric' defined as decimal(5,0). Now suppose one ID is: '56007'. I want to be able to tell how many rows in the entire table have that ID. I know that I can use the select keyword, but i'm wondering if there's another way, because the table i'll be doing this for can be up to 350 Million rows, and the vast majority (probably close to 99.9%) of the rows will have mutually exclusive IDs. I am only concerned with that small percentage of rows that have duplicate IDs
View Replies !
Calculating Totals From Two Tables
I have three tables: invoices, invoicedetails, invoicepayments The fields are: invoices -------- InvoiceNo InvoiceDate CompanyNo invoicedetails -------------- InvoiceNo ProductNo Quantity UnitPrice invoicepayments --------------- InvoiceNo PaymentDate PaymentAmount For each row in invoices there will be 0 or more rows in invoicedetails and invoicepayments, with the InvoiceNo field linking everything together (i.e. one to many relationship between invoices and invoicedetails and invoicepayments). I need a query that will give me a list of invoices that still have money outstanding on them. To manually do this I would loop through the invoices table and for each InvoiceNo I would gather all the matching rows in invoicedetails and invoicepayments. To get the invoice total I would multiply Quantity by UnitPrice for each invoicedetails row. To get the total paid I would add up the PaymentAmount. Then I'd compare the invoice total and the payment total and if the payment total was less than the invoice total I'd know that there was still some money outstanding on that invoice. Now, how do I write a single query to do this? I using MySQL 4.0.20 (unfortunately because I don't control the server I can't upgrade to a version of MySQL that support subqueries). I'm guessing I'll need to use the SUM() function to add things up, and GROUP BY to group the invoicedetails and invoicepayments so I only get one row per invoice. I can get a total for each invoice by using the following query: SELECT invoices.InvoiceNo, SUM(Quantity * UnitPrice) AS InvTotal FROM invoices, invoicedetails WHERE invoices.InvoiceNo = invoicedetails.InvoiceNo GROUP BY invoices.InvoiceNo However I'm at a loss as to how I would modify this query to also total up the invoicepayments to give me a PaymentsTotal and then calculate the difference between the InvTotal and the PaymentsTotal to figure out if there is still money outstanding.
View Replies !
Getting Totals(or Percentage) Of Each Field
Say i have a select statement which selects 5 fields and displays the results as follows: field1 | field2 | field3 | field4 | field5 10 | 20 | 60 | 80 | 40 10 | 20 | 60 | 80 | 40 10 | 20 | 60 | 80 | 40 10 | 20 | 60 | 80 | 40 10 | 20 | 60 | 80 | 40 how can i add one more row to the output, which would calculate the total of each field and display at the bottom of the table?? for above example, the output should look like: field1 | field2 | field3 | field4 | field5 10 | 20 | 60 | 80 | 40 10 | 20 | 60 | 80 | 40 10 | 20 | 60 | 80 | 40 10 | 20 | 60 | 80 | 40 10 | 20 | 60 | 80 | 40 Total 50 | 100 | 300 | 400 | 200
View Replies !
Computing A Difference In Totals.
I am using php/MySQL 4.0 .. If I had a table consisting of: team l points ------------------------------------- teamA l 15 teamA l 10 teamA l 5 teamB l 5 teamB l 10 teamC l 5 if (mysql_query("SET @rank = 0;", $conn)) { if ($result = mysql_query("SELECT @rank := @rank + 1 AS Rank, team, SUM(points) as ttl FROM table GROUP BY team ORDER BY ttl DESC, TM ASC;", $conn)) How would I get the result below? rank l team l points l behind -------------------------- 1. teamA 30 0 2. teamB 15 15 3. teamC 5 25 I know how to implement the ranking of the teams .. I do not know how to get the point difference for the behind column. Is there a way to set the ranking to handle ties? (Example: 1,2,2,4,5 etc.)?
View Replies !
Showing Totals And Subtotals In One Row
I have a table with the following fields: ContractID | CustomerID | ProductID | Quantity For each Contract there is one record: Who has ordered which product in what quantity. Now I'd like to generate a report that shows: - which products were ordered (SELECT ProductID ... GROUP BY ProductID) - at most (SELECT ... SUM(Quantity) AS Quantity ... ORDER BY Quantity DESC) - and from which customers. (SELECT CustomerID, Quantity ...) GROUP_CONCAT(...)? Subquery? Sample-Output: P_ID - Quantity - Customer's quantities ---------------------------------------- 1230 - 10'000 - A: 2'000, B: 8'000 1240 - 8'000 - A: 7'000, C: 500, D: 500 1120 - 6'000 - C: 6'000 ... How shall I build the SQL statement?
View Replies !
Getting Totals From 5 Tables In One Query...
This is something that has been puzzling me for a few weeks. I have 5 tables in the database that I want the total rows count from. Now I know I could do 5 queries and use 5 mysql_num_rows to return the result but I feel sure that there is a better/easier/more efficient way of doing it. i am guessing that it has something to do with joins but mysql really isn't my thang! I Have tried something like: select count(hotel.id) as atb, count(trainer.rec_id) as det, count(club.id) as ml, count(activity.id) as sl from hotel, trainer, club, activity but that returns the totalled amounts added together which is why I figure a join is needed. My aim is a online stats panel something like: Leisure Clubs Onsite: 2442 Trainers Onsite: 232 Hotels Onsite: 1978
View Replies !
Select Statement, Grouping By Totals
I have an enormous database and I'd like to count how many times a unique record appears, then order the results based on that. For example: select a, b, count(a) AS TOTAL from table GROUP BY a ORDER BY TOTAL DESC; +-----------+------------+----------+ | a | b | Total | +-----------+------------+----------+ | z | 2004-01-14 | 24 | | x | 2004-01-05 | 22 | | b | 2004-02-11 | 20 | ------------------------------------- Meaning z appeard 24 times, x 22, and so on. This only returns the totals, not each row in itself. I need to have each row returned based on the amount of times it appeared. I obviously have to keep GROUP BY in there so I'm unable to ORDER BY TOTAL returned.
View Replies !
Selecting Totals For Multiple Dates
I have a form where a user can input two dates and I want to get a sum of the day's data for each of the days separately. So far the closest I've come is: // to display the total for one day SELECT sum( hplmnmoc ) FROM `inRtccCallType` WHERE host='wilsle03' AND date='2007-05-25' OR // to display the total for all days SELECT sum( hplmnmoc ) FROM `inRtccCallType` WHERE host='wilsle03' AND date BETWEEN '2007-05-24' AND '2007-05-31' With what I have so far I can either display one day's total or else a total for the whole period. Can anyone tell me how to get the totals for each day individually without having to perform multiple queries.
View Replies !
Totals Query Based On Days
If I have a table with a ProductID, Quantity, & DateTime field, & would like to have the sum of the Quantity calculated per product per day with blank days being accounted for even if zeroed out, how would I go about accomplishing this in one query? Example result for ProductX:
View Replies !
Fiscal Year Totals - How To Calculate?
I am given the month number for the fiscal year. For exmaple, "4" indicates the fiscal year begins April 1 each year. April 2, 2005 would be Fiscal Year 2005. March 30, 2005 would be Fiscal Year 2004. With the following table structure: TABLE_A id date amount My current set up is like this, based on calendar year: PHP // get a list of years in the db $years = $dbh->getCol("SELECT DISTINCT(YEAR(r.date)) FROM table_A r ORDER BY YEAR(r.date) ASC"); foreach ($years as $s) { $yearSum = $db->getOne("SELECT SUM(r.amount) FROM table_A r WHERE YEAR(r.date) = '$s'"); //echo something here } ISSUE1: I need to calculate the total for each fiscal year. For example, April 1, 2004 - March 31, 2005. AND April 1, 2005 - March 31, 2006, and so on and so on for all years. ISSUE2: I need to calculate the total for each MONTH within each fiscal year. For example, during the fiscal year April 1, 2004 - March 31, 2005, what was January's total, Feb's total,... For each fiscal year.
View Replies !
Gathering Totals From Multiple Tables
I've got a little bit of an issue which I need a little bit of mysql-guru help with. I want to get the top 5 users who have authored the most articles, and how many articles each has authored (total). The problem is, I have 3 tables in which I store the articles. I've got articles_faq, articles_kb, and articles_ref. Each of these has an auto_increment field 'id', and an 'author' field (there's obviously more fields, but they aren't relevant). How would I get this data in a single query? Is it even possible using MySQL? Although I'm improving my MySQL skills and knowledge quite a bit, this is beyond what I'm capable of, that's for sure.
View Replies !
Query To Return Totals Of 1-5 Votes, Even If That Number Is 0
query to return totals of the votes: SELECT count(good) as numgood, sum(distinct good) as scoregood, sum(good) as totgood from survey where jobclass = 'Salaried' group by good That returns this: numgood | scoregood | totgood 27 | 4 | 108 70 | 5 | 350 What I need are results that include no votes cast for the other values: numgood | scoregood | totgood 0 | 1 | 0 0 | 2 | 0 0 | 3 | 0 27 | 4 | 108 70 | 5 | 350
View Replies !
Viewing Date Range Then Adding Column Totals?
this is probably my most complex question to date. Basically i have a table that stores order information for products. What i need to do is: - Specify a Date range - Count number of rows in that range - Get column totals for that range - Return Array with column totals eg, if the array was named $total, $total['column1'] would be the column 1 total :) This is a large table with many columns so here is what i had planned: //OPEN CONNECTION HERE, SET DB //First query gets date range: $result = mysql_query("SELECT * FROM D_Orders_Columbus WHERE odate > '" . $startdate . "' AND odate < '" . $enddate . "'"); //now we get number of rows: $num_orders = mysql_num_rows($result); After that i get stuck, i need it to ADD the column values together, for this i assume i will need to set the column types to 'SMALLINT' (i dont assume anyone will order 32000 items :p). How can i get mysql to total all the columns that can be (eg. have number types) and then return an array with the totals?
View Replies !
How Do I Generate Results Based On Totals Of Another Table But For 1st Table?
This is what I want to do: 1- I have Two tables: polls_created and votes 2- Table polls_created is like: poll_id owner poll_subject 3- Table votes has the votes issued for a given poll, like this: vote_id poll_id vote vote_date So what I need to do is to look at these 2 Tables and generate results based on values of these 2 tables. How do I then generate this result: MySQL Code: SELECT poll_id, owner, poll_subject, COUNT(vote_id) AS number_of_votes FROM polls_created, votes "sorted by polls that have gotten most number of Votes" Of course "sorted by polls that have gotten most number of Votes" is not real MySQL
View Replies !
Querying For Transaction Totals And Last Transaction Date
I have a list of currency transactions made by users. I need to generate a list of users along with their transaction total (sum for each user) AND the date of their last transaction. Sound doable? MySQL 4.1 Data looks like this: user, amount, date ============== 1, 50, 2003-11-23 2, 34, 2004-10-04 3, 45, 2005-08-30 3, 98, 2006-04-02 3, 76, 2000-02-03 2, 91, 2000-12-04 1, 11, 2003-11-05 3, 22, 2003-03-06 4, 34, 2006-03-07 5, 45, 2006-06-24 I figure I can group by userID but how do I get the date of the most current transaction? Using the data above, the query would return: 1 (user) 61 (subtotal) 2003-11-23 (last transaction) 2 (user) 125 (subtotal) 2004-10-04 (last transaction)
View Replies !
"Totals" Row For Columns
Looking for the result below from the sample table listed. Using MySQL 4.0/php. ROLLUP is not supported in my version of MySQL ... table1 -------------------------- Person l Units l Amount -------------------------- person1 l 7 l 11 person1 l 8 l 11 person2 l 13 l 6 person2 l 13 l 7 person3 l 11 l 7 SELECT Person, SUM(Units), SUM(Amount) FROM table1 GROUP BY Person person1 l 15 l 22 person2 l 26 l 13 person3 l 11 l 7 "totals: l 52 l 42" = desired result
View Replies !
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?
View Replies !
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?
View Replies !
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?
View Replies !
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!
View Replies !
Counting 2 Tables
I have 2 tables, with the same column names that i need to count. Where i try to do this run query: SELECT name, COUNT(name) FROM users, place GROUP BY name I get this error: #1052 - Column 'name' in field list is ambiguous
View Replies !
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
View Replies !
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,
View Replies !
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?
View Replies !
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?
View Replies !
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.
View Replies !
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
View Replies !
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?
View Replies !
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.
View Replies !
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.
View Replies !
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: ....
View Replies !
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.
View Replies !
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.
View Replies !
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?
View Replies !
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?
View Replies !
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 Replies !
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?
View Replies !
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.
View Replies !
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 ....
View Replies !
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]
View Replies !
Counting Rows In A Table
Anyone know how to count rows in a table depending on 2 columns, and different values within the second column. Basically i am trying to obtain the resultset example below from the table example below TABLE example: Date | Status ------------------------- 2003/07/01 | 1 2003/07/04 | 1 2003/07/06 | 1 2003/07/24 | 2 2003/07/24 | 2 2003/07/24 | 1 RESULTSET required: Date | Status = 1 | Status = 2 ----------------------------------------------- 2003/07/01 | 1 | 2003/07/04 | 1 | 2003/07/06 | 1 | 2003/07/24 | 1 | 2
View Replies !
Counting Queries In Php/mysql
I've seen some pages with the footer: This page was created in 0.003s using 123 queries. Is there a way to get the number of queries on a single connection? I could fetch the value of "SHOW STATUS LIKE Questions" at the start and end of the page, but that would also count queries from other pages running at the same time, on a busy server. But even if it is possible to get the number of queries on a single connection, I think there is the risc of php using the same connection for other pages being created at the same time. Or is the query count done with some sort of wrapper around mysql_query? function my_query($sth) { $number_of_queries++; return mysql_query($sth)
View Replies !
Counting By The Unique Values
Put simply I want to count how many rows there are in the database with each value for the collum such as the collum 'username' (e.g. how many rows have evenstar7139 for 'username'?). When this code, whatever it is, is run through the database I should have numbers for how many rows each different username has. (e.g. evenstar has 207, nova has 225, roonie-roe has 98, etc.) this also needs to order the results by highest number first descending to lowest number last.And if I could limit it by something like the 10 highest that would be great too.
View Replies !
Counting Number Of Rows
Can anyone come up with a novel way of counting the number of rows in a table that contain no NULL values in the columns that each row contains? Each row contains 200+ columns, so doing a "count" on each column, and then selecting the smallest value is not very practical.
View Replies !
Counting Records For Downloads
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?
View Replies !
GROUP BY And Counting Rows
SELECT id,count(*) as c FROM table WHERE id='$_GET[id]' GROUP BY id The following produces: 1009 8 1010 9 1011 11 "c" is coming back with 8, but that's not what I want. I want the total number of rows returned which in this case would be 3. How can I restructure this query to return the total number of rows while still allowing me to GROUP BY? Is this even possible? How should I proceed?
View Replies !
|
TRACKER
