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 Complete Forum Thread with Replies
Related Forum Messages:
Number Of Votes
This is how my db looks: id service type ip rating 109 Share Ad Space te 212.123.141.157 4 108 Traffic Syndicate 25 te 212.123.141.157 4 114 Profoundhits as 212.123.141.157 5 118 Profoundhits as 213 5 A user rates a service and for every vote he makes an entry goes into the db. With this: Code: SELECT service, AVG(rating) AS rating FROM rate WHERE type = 'as' GROUP BY service ORDER BY rating DESC LIMIT 3 i get the average but i also want the number of votes and the service with the most votes and the highest rating should be placed 1st. How do i do this?
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 !
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 !
How To Return A Row Number?
Using MySQL 5.0 CF8 DW CS3 I guess as usual this is a really simple question requiring a really simple answer but my head has a black hole at the moment so if someone could help me out I would be greatful I have a query: <cfquery name="rstrdt" datasource="cdsl"> SELECT * FROM players ORDER BY winpercentage DESC,played DESC,banktotal DESC </cfquery> the table the query creates has many colums but 2 of the columns are 'player' & 'club thus my question is: how would I return the row number where the player and his club are in the table, the primary key 'playerindex' identifies the player uniquely so that is of no use to me in this instance.
View Replies !
Return The Row Number
I have a problem. I need to rank data from highest to lowest, then return the value of what rank a certain piece of data is For example id value 1 20 2 80 3 60 4 120 5 10 So I need to make a query that organizes this by value 4 120 2 80 3 60 1 20 5 10 Then if my query says "for ID 1" it will return the value "4" since it is ranked 4th. Is there any easy way to do this with a query? I am using PHP as my programming language, if theres a way of doing it with PHP can someone direct me to it?
View Replies !
Return Auto_inc Number
is there a command that will will return the auto increment number of a insert at time of insert?what i've got is a table that has a client_id field that is set to auto increment. I'd like to insert a new client and with the same query return the assigned auto increment number if possable.is this something can be done or do i have to run another query to get that number?
View Replies !
Regexp To Return All Number Inside A Field
does this is possible? all i just want to manipulate is the phone number in my database. i want to get the numbers inside this format text (125) 125-1268 or in this format 225.265.4649. how can i accomplish that in the query?
View Replies !
Return A Count Of Number Of Rows Before Desired Row.
Anyone know how? find_in_set doesn't seem to be able to do what I want since I am not searching through an array but am actually running a query. What I really want is to be able to select all until something = something. I've searched the realm of google but cannot come up with anything useful, and so I am wondering if maybe I don't know the right words to be looking for.
View Replies !
Find Similar Entries - Return Entry And Number
I have data in the form of random words, with some repeats. Can I write a query that will return the most frequently returned word and how many times it occured? I am just looking for a starting point... Also, will it be possible to return the next 4 records in order. For example, if the word "me" was found 14 times and the word "toad" was found 12 times, it would return "me" and 14 and "toad" and 12?
View Replies !
Percentage Of Votes
i made a script for my buddy that his clients can vote and wright comments on his service. in my database i have 3 fields vote comments web so vote is the votes rated and web is the rates rated for the web site. now i want to extract the total percentage of votes? i have a start..from searching on Google mysql percentage SELECT (SUM( votes ) / COUNT( votes )) *100 percentage FROM `votes`
View Replies !
ORDER BY Votes Then DATE
I need to write an SQL query so that I can show data sorted like this: If the field (votes) has a value > 0 then put these records at first (ORDER BY votes), then other records are ordered by date entered such as the latest is on top.
View Replies !
Group Votes Into Low, Medium And High
I have a table with votes: --------- | vote | | 5 | | 1 | | 9 | | 6 | | 2 | | 1 | | 3 | --------- And I would like to see number of votes between 1-3, 4-6, 7-10 This select doesnt work: SELECT count(vote<=3), count(vote>=4 AND vote<=6), count(vote>=7) FROM user_bfcore_vote;
View Replies !
Order By Division :: Ratings And Votes
I would like to write a query that returns rows based on the division of two columns. For example, the database holds # of votes and total rating, I would like to order by the average rating (total rating/# of votes).
View Replies !
Organizing Members By Votes (seperate Table)
Got another DB question for you... still getting the hang of more complex DB queries. I have a table of: MEMBERS: userid, username, password, etc. VOTES: userid, voterid Please note that the votes table can have multiple votes per user, etc. So what I want to do is, list my members in order of how many votes they have in the VOTES table.
View Replies !
Query Count Return Zero
I'm trying to query a database searching for everytime an event happened in the past 24 hours. I'm using a count(eventid) to add up each event during an hour time. However, if an event didn't happen say for 2AM then 2AM isn't shown in the return. How can I get 2AM to show as a row, but with a count of 0 (zero) ?? Here is my qeury... select count(eventid), DATE_FORMAT(CONCAT(eventdate,' ',eventtime),'%Y%m%d%H') as hour, time_format(eventtime, '%H:00'), eventdate from event where sensorid = '2' and eventdate between '2005-01-19' and '2005-01-20' and DATE_FORMAT(CONCAT(eventdate,' ',eventtime),'%Y%m%d%H%i%s') >= '20050119150018' group by hour order by hour desc;
View Replies !
Query To Return Two Count()s
I'm trying to figure out a better way of doing this: SELECT (SELECT COUNT(*) FROM GuestEvents WHERE EventID = 1 AND EventStatus = 'y' AND UserID = 'bob') AS Event1, (SELECT COUNT(*) FROM GuestEvents WHERE EventID = 2 AND EventStatus = 'y' AND UserID = 'bob') AS Event2 As you can see it's quite repetitive, I wondered if there isn't something like this which could optimise it a bit: SELECT COUNT(EventID=1) AS Event1, COUNT(EventID=2) AS Event2 FROM GuestEvents WHERE EventStatus = 'y' AND UserID = 'bob'
View Replies !
The Query Cache Does Not Return Stale
I am reading up on query cache feature and would like to have the following question clarified. From mysql 5.0 manual, section 5.14, it says "The query cache does not return stale data. When tables are modified, any relevant entries in the query cache are flushed." I would like to know what is the definition of "when tables are modified". does it mean? 1. new records inserted into table? 2. records gets updated? 3. table structure is modified by using alter table command?
View Replies !
Default MySQL Return Value For Query
I'm writing a multilingual web site where not all content will necessarily be multilingual. I have a languageid and want to retrieve the content for that language. However, if that content doesn't exist, I would want to retrieve content for the default language. My ideal query would be something like: SELECT content FROM page_content WHERE pageid=$pid AND languageid=$userlang IF NO RESULTS WHERE pageid=$pid AND languageid=$defaultlangid I imagine my made- up 'IF NO RESULTS' directive does not exist. Is there any way to get that kind of functionality from one query? Since I will be having queries all over the site to retrieve different kinds of localized things (image tags, html code, textual content), it would be awesome if I could figure out a way to consolidate. Maybe there is a way to sort results from this query: SELECT content FROM page_content where pageid=$pid AND (languageid=$userlang OR languageid=$defaultlang) ORDER BY .... ? I'm also not sure if the nested boolean will work.
View Replies !
Return Info On Last Record With One Query
Lets call the table: comics Lets call the fields I am looking for: ID filename I want to get the maximum ID from the table and the filename field corresponding to that maximum id. I can do it easy in two queries: select max(ID) as max_id from comics then in php send select filename from comics where ID=$id (where $id is the number returned from the first query) I would like to do ONE query like: Code: select max(ID) as max_id, and filename from comics where ID=max_id
View Replies !
Column Name Is SQL Keyword(Return) In SELECT Query
Is there a setting in MYSQL server that let it accept Select queries with Column names that also are keywords? "Return" is a keyword now in MySQL. Motivation: We had an old delphi application that uses a mysql database. Somehere in code it does: SELECT Return from CarUse; (Actually it does more but the above is the isolated problem.). Return is a Column name in the CarUse database. In our old setup with an old mysql database, the above code was accepted. I migrated the database to a new server with a new MySQL. The above code is no longer accepted.
View Replies !
DateTime Query To Return Last Months Transactions
For the sake of example, let's use the following table. I want to return all txn_ids that occurred last month. I'd like the function to not care how many days there were last month, but still return the correct values. Table: transactions timestamp : DateTime txn_id : int I've done some reading, but everything I've found seems to use actual dates to do the subtraction. This requires that you know how many days are in that month. The other way requires that everything be referenced to the current date. SELECT * FROM txn_id WHERE timestamp BETWEEN '2005-01-01' AND '2005-12-31' SELECT * from txn_id where timestamp >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
View Replies !
Creating A Query That Will Only Return Records With Matching Counterparts
I'm using the table below as an example. I want to create a MySQL query that will return only records that have matching counter-parts where 'col1' = 'ABC'. Notice the 'ABC / GHI' record does not have a counter-matching 'GHI / ABC' record. This record should not be returned because there is no matching counter-part. With this table, the 'ABC / GHI' record should be the only one returned in the query. How can I create a query that will do this? id | col1 | col2 -------------------- 1 | ABC | DEF 2 | DEF | ABC 3 | ABC | GHI 4 | DEF | GHI 5 | GHI | DEF
View Replies !
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 Replies !
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 !
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 !
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 !
Lost Connection To MySQL Server During Query Socket Error On Read. WSAGetLastError Return 10054($2746)
We just migrated our MySQL database from a NetWare server running version 5.0.21 to a openSUSE box running version 5.0.45. Everything ran fine for a couple of days, then clients began losing their connections and creating new ones without the old threads being terminated. This behavior is the same for the Delphi client we use to access our database as well as the MySQL Administrator tool. I've tried adjusting connection_timeout, but overall the settings for this server are the same as the previous one that ran without issues for a year.
View Replies !
Number Of Replies Query
I have a forum using parent id and qid(auto increment). Parent id will be set "0" if this is a new thread, otherwise, parent id = qid (reply). In my index page, I can read the parent thread just as usual using select * from forum where parent id = 0 order by last update date. But I want to know how do I also display the num of replies to those replies also. I am using a while loop to generate the parent thread as while ($row = $result->fetchRow()) { echo"$q_id = $row[0];"; echo"$forum_type= $row[1];"; echo"$user_id= $row[2];"; echo"$title = $row[3];"; echo"$body = $row[4];"; echo"$last_upd_date = $row[5];"; }
View Replies !
Get Category And Number Of Items In One Query
I have a `category` table with columns cat_id and cat_name. And another `items` table with columns item_id, item_name and cat_id. I want a query which will select display each category name and the number of items in that category. I could do this in two sql statements, is it possible to do it in one?
View Replies !
Setting Number Format In Query
This might be an easy one, but I can't seem to find how to do this. I'm computing an average in a query, and I need to format the result for display in a php generated webpage. The values being used for calculating an average are real(4,1), but my average comes out with a precision of 5 decimal places. I'd like to show only 1 decimal place.
View Replies !
List The Number Of Records In Query
I have a very simple query. I want to list a bunch of names & how old they are. (two columns) I want to add a third column that basically counts how many records there are. Example: 1. Bill Jones - 55 2. Bob Smith - 56 3. Steve Gates - 25 Can I do this in mysql or should I add the code in PHP, and how do I do it?
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 !
Does Query Speed Depend On The Number Of Coloms?
Does the query speed also depend on the colums that you don't use in the query (so not in the where and not in the select part). I have a lange unused backup field in every row. I only put the backup information into that field once. But never use it on my live database. Does it speed up my queries if I put the backup information into an other table?
View Replies !
Find Hightest Number After Finding First Query
i have this code but it does not display the highest number or anything. it should be searching the field 'name' for the string $name and only display the one that has the highest number in the 'number' field. what am i doing wrong? $res = mysql_query("SELECT MAX( number) FROM data WHERE name='".$name."'"); $high = mysql_result($res, 0, 'number') or die(mysql_error()); echo($high);
View Replies !
Using A Query To Determine The Maximum Value A Number Col Can Hold?
I'm trying to find out how can you determine the largest value you can enter into a column of type smallint,mediumint,int, etc using a query. I know what the values are by looking at the documentation, but I would like to get this number using a query at runtime. So when I insert values into different tables/columns I can use an appropiate restriction befor trying to insert.
View Replies !
|
TRACKER
