Calculating And Displating Voting Results
The results pages of myverdict.net have been the most difficult so far. It took me a month of research to find out how to write the sql query. I eventually found the answer in an added comment in mysql documentation. Here was my problem. I had a table with a vote column, the entries for which could be For, Against or Undecided. Counting the total votes was easy enough and by grouping I could return a count for each. However, I wanted to display results for every question in a particular category, on one page, using a repeating table, the results reading across the page. A repeating region only displays one row at a time and my simple grouped query would not do as it returned multiple rows.
Here then, for the sql buffs out there is the query that worked.
SELECT questions.question, COUNT(votes.vote) AS total,
COUNT(votes.vote = ‘For’ OR NULL) AS col1,
COUNT(votes.vote = ‘Against’ OR NULL) AS col2,
COUNT(votes.vote = ‘Undecided’ OR NULL) AS col3
FROM questions, votes
WHERE questions.questionID = votes.questionID
GROUP BY questions.question
You apparently need the ‘OR NULL’ or else it doesn’t work, I don’t know why. Anyway it was a simple matter to display col1, col2, col3 and total votes in the repeating region of my page.
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Voting System
*********************** | game_id | discussion_id | *********************** SELECT game_id, discussion_id, count( * ) FROM `games_voting` AS votes GROUP BY votes.game_id, votes.discussion_id using the above query to generate a count of votes grouped by game and then user who got the votes right now it returns all the users that received votes for each game, i'd like to only receive the user with the top vote count
View Replies !
View Related
Calculating New Vs Old
How can I construct this query? I have a bunch of values in a table. I want to output the % of values that only occur once to the % of values that occur more than once. In other words, if I have 1, 2, 2, 2, 3, 4, 4, I would like to output: New numbers: 50% Existing numbers: 50% ..new numbers because the 1 and 3 only occurred once and existing numbers of 2 and 4 because they occurred more than once. %s are %s of individual numbers.
View Replies !
View Related
Calculating Average Age
I got this players table and I do want to list its average age... SELECT AVG(YEAR(SUBDATE(CURDATE(), TO_DAYS(birth)))) FROM players This works fine for me BUT, it doesn't on my server though it isn't 4.1.XX as it is on my computer at home.
View Replies !
View Related
Calculating A Percentage
I'm working on an adp file and I'm trying to do is calculate what percentage one figure is of another figure in a view. In access I would simply do it like this: [FirstField]/[SecondField]*100 But this doesn't seem to work. My exact code is as follows: SELECT Product, Quarter, TotalPlanProductSpend, TotalPlanSpend, TotalPlanProductSpend / TotalPlanSpend * 100 AS PercentOfTotal FROM dbo.Qry_RptPlanPRoductSetup
View Replies !
View Related
Calculating Median
I have to find out median amount of loan amount. so can i do it in a query. I know its possible by writing function. but still i am asking.I am new in MYSQL.I have searched for inbuild functions.i didn't found any median function.
View Replies !
View Related
Errors Calculating Vat
Why do I get the wrong results with the code below. iAmount = 16.84 iVatAmt = ((iAmount * 17.5) 100) (Returns 2.00 instead of 2.947) iTotalAmt = iAmount + iVatAmt (Should = 19.79 but comes back with 18.84) I know that the "" means interger division but if I use "/" I keep getting errors reported from PayPal that the Amount being passed to it is formatted incorrectly. How do I calculate and add the vat element to the iAmount keeping the correct decimal format.
View Replies !
View Related
Calculating A New Field
Lets say I have the following price margins: price------percent margin between $1 and $5---- 60 - 80 between $20 and $40---- 50 - 60 The following works as expected but how do I accomplish the above? select `c`.`eachprice` AS `eachprice`,`c`.`caseprice` AS `caseprice`,(`c`.`caseprice` - `c`.`eachprice`) AS `caseminuseach` from `UNFIW` `c` where ((`c`.`eachprice` between 12 and 24) or (`c`.`eachprice` between 1 and 4)) Something like this? select `c`.`eachprice` AS `eachprice`,`c`.`sku` AS `sku`, if `c`.`eachprice` between 1 and 2.5 ((`c`.`eachprice`*.60)+`c`.`eachprice`) AS `custommargin` and if `c`.`eachprice` between 2.5 and 5 ((`c`.`eachprice`*.80)+`c`.`eachprice`) AS `custommargin` from `UNFIW` `c`
View Replies !
View Related
Calculating Time
I have a small application which sends out alerts at specified times during a day. I have a table with 3 fields :- id int(11) alertime datetime sendbefore datetime These alerts will get automatically sent out depending on how many hours the user specified in the sendbefore field. For example if the alertime is set to '04/02/2006 15:00:00' and sendbefore is set to '03/02/2006 15:00:00' then that alert needs to go out 24 hours before. I need an SQL query or stored procedure which will return the alerts to be sent out during each hour of the day. And i am not sure how to do this. Hope it makes sense? I dont mind making the sendbefore field into an int so that i only need to put in number of hours before the alerts should go out.
View Replies !
View Related
Calculating Moving Difference
I got 2 records by individual with some fileds, one associates with the min date and the other one assocaites with max date. So lay out looks as follows: Key_ID | Dates_Min_Max Avg_Weight 1234 1/2/2004 12 1234 1/2/2006 24 I need to get the difference change between the weights for individual ids that is group by Key_ID and find the percent change. Some thing like this: (MAx_Row - Previos Row)/Previous Row * 100. How to do this? How do I get the previos row between 2 records if I order by the date?
View Replies !
View Related
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 !
View Related
Calculating The Sum Of Various Sections Of A Table
I have a table that has two columns. the first column has product names and the second has product quantities. The product names are similar to the example below. 01BlueChair 01BlueTable 01GreenChair 01GreenTable 01RedChair 01RedTable 02BlueChair 02BlueTable 02GreenChair 02GreenTable 02RedChair 02RedTable Each product name has a number, a colour and an item type. What I need to do, is calculate the sum of the quantities for each number and product type. Eg, how many 01Chairs do we have? But I need to do that for 01Tables, 02Chairs and 02Tables as well, and output them in the same query results. 01Chair 12 01Table 15 02Chair 11 02Table 21 Some like the above. I can do it for one item, but not for all, for example here is the code I am using that outputs one result. Code: select stock-code sum(stk-trans-qty) from stock-movements where and stock-code like "01%Chair"
View Replies !
View Related
Calculating Business Days
I need to calculate the number of business days between a pair of arbitrary dates.Some scrounging around online delivered me this query,which basically does the trick: Quote:SELECT d1, d2, @dow1 := DAYOFWEEK(d1) AS dow1, @dow2 := DAYOFWEEK(d2) AS dow2, @days := DATEDIFF(d2,d1) AS Days, @wknddays := 2 * FLOOR( @days / 7 ) + if( @dow1 = 1 AND @dow2 > 1, 1, if( @dow1 = 7 AND @dow2 = 1, 1, if( @dow1 > 1 AND @dow1 > @dow2, 2, if( @dow1 < 7 AND @dow2 = 7, 1, 0 ) ) ) ) AS WkndDays, @days - @wkndDays AS BizDays FROM dates ORDER BY d1,d2;
View Replies !
View Related
Calculating Time Difference
I have a task where I need to calculate the hours between two dates. However, they only want to calcute the time during Mon-Friday from 8-5. So if someone enters a question at 4:59PM on Friday and someone responds to them on Monday at 8:30. The user wants to see 31 minutes as the response time. I see that I have a function called dayname to get the Monday-Friday. However, I have no idea how I might actually use this to calculate the time lapsed. I need it to somehow ignore Saturday/Sunday.
View Replies !
View Related
Calculating Dates From Other Tables
below is some code we have created to enter in payment details for a customer.... INSERT into Payments values(Payments_seq.nextval, initcap('&Payment_Method'), '&Amount_Payable', Date_Due = (select date_of_order from orders where order_no = (date_of_order+7)); I am having trouble with the last line, I want the date due to be calculated from the date the order was made in another table (orders) and I want a week to be added to this so that in the field it will display (date ordered plus 7 days)...
View Replies !
View Related
Calculating Date Difference With PHP
I know nothing about MySQL (I'm more of a low level php guy). I'm building a reminder script that will remind the site admin to send out a newsletter. I want to send my reminder email email only if 30 days have passed since the last newsletter sending. I've already setup a database that will be updated with the current date when a newsletter is sent out. I found this function, which seams useful. PHP mysql> SELECT DATEDIFF(��-12-31 23:59:59',��-12-30'); -> 1 My guess is I could do something vaguelylike this: PHP $x = SELECT DATEDIFF(' CURRENT_DATE()',' the date in the dabase'); -> the difference between the 2 of them From there I could use php to say if $x is greater than 29 days, send the email. I was wondering if anyone could help me with the syntax / methodology of achieving this. If MySQL is not the right method, please let me know. I was told that MySQL is the "best" way of doing this.
View Replies !
View Related
Calculating Innodb Space ?
I have just started with some innodb. So first I say there was a file called ibdata1 of size 10Mb. So as I add on data it became 18Mb now. So I want to find out is how to exactly know what is size of my innodb database.
View Replies !
View Related
Calculating Disk Space
I was wondering, is there a way to calculate the overhead in disk space for a table that contains column types that are only regular ints and floats. The table type is MyISAM, but I'd also like to know for other tables how to find this overhead. Basically I'm looking for a formula. If that's possible?
View Replies !
View Related
Calculating Duration Between Specific Row Entries
Table: CREATE TABLE `log` ( `id` int(11) NOT NULL auto_increment, `session_key` int(11), `date` datetime, `level` varchar(30), `action` varchar(100) NOT NULL default '', PRIMARY KEY (`id`) ) TYPE=MyISAM; - Every user has a unique 'session_key' - Actions can in principle come in any order - A user can have several 'game started' - 'game ended' pairs with the same session_key. A typical log set of log-entries would look like: 1,1000, 2008-11-01 10:00:00,level2, game started 2,1001, 2008-11-01 10:01:00,level2, event3 3,1001, 2008-11-01 10:02:00,level2, event2 4,1002, 2008-11-01 10:02:30,level2, event2 5,1001, 2008-11-01 10:03:00,level2, game started 6,1001, 2008-11-01 10:04:00,level2, game ended 7,1000, 2008-11-01 10:05:00,level2, event3 8,1000, 2008-11-01 10:06:00,level2, game ended 9,1000, 2008-11-01 10:07:00,level2, game started 10,1000, 2008-11-01 10:08:00,level2, game ended The query should give the following result session_key, level, duration (hh:mm:ss) -------------------------------------- 1000,level2,00:06:00 1001,level2,00:01:00 1000,level2,00:01:00
View Replies !
View Related
Calculating A % Based On Date Range
I have a table called TransResult and I have 2 data fields in it. I want to get the percentages for the 2 possiable values in TransResult and I want this done by date, so I can go backwards to compare what was done in the past months to what is done in current month. This is what i have come up with so far, however my _total is giving me ALL records in the table not this months records, so my percentages are off, aside from the _total the values it is calculating are correct. SELECT TransResult, COUNT(*) AS HowMany, (COUNT(*) / _total ) * 100 AS Percent FROM tbltranslog, (SELECT COUNT(*) AS _total FROM tbltranslog) AS myTotal WHERE MONTH(Date) = MONTH(NOW()) +0 GROUP BY transresult
View Replies !
View Related
Calculating Days To Excluding Weekends
I have a table(t2) with the following fields t2 Fields Datatypes StartDate datetime EndDate datetime #_of_days_Taken decimal A user will select start date and end date , So I want the system to automatically update #_of_days_Taken but it needs to excludes weekends only. I currenntly have it like this --> #_of_days_Taken =(EndDate - StartDate) If anyone knows how to do it please show me how to go about it.
View Replies !
View Related
Calculating Unused Time, Per Day Given Date Ranges
I've found a few potential solutions to this, but mostly they use Oracle Analytics syntax or SQL server specific extensions. I'm struggling to come up with something that works in Mysql. I've got a table of date ranges, let's call it "bookings" Code: roomid | startdate | enddate -------+------------------+----------------- 1 | 2008-02-03 13:00 | 2008-02-03 17:00 1 | 2008-02-03 18:00 | 2008-02-03 19:00 I'm trying to come up with a query that will give me the number of unused minutes for a room for a given day, such as: Code: roomid | date | unused -------+------------+------- 1 | 2008-02-02 | 1440 1 | 2008-02-03 | 1140 1 | 2008-02-04 | 1440......
View Replies !
View Related
Using Only Weekdays And Excluding Weekends When Calculating Dates
I need to figure out how many days are between certain dates excluding weekends. Is it possible to do this. I have tried searching google, but I guess I am not using the right keywords because the results I am getting back aren't giving me much help. I was thinking about setting up a table and putting in all weekend dates and then pulling in that data, but thought there might be an easier way.
View Replies !
View Related
Calculating Distance With Latitude/longitude In MySQL
I was trawling around the web and discovered the following gode on a website called http://ben.milleare.com and Ben is an english guy who has produced the following MySQL snippet which calculates the distance between two sets of Long Lat points. SELECT id,name,(((acos(sin(($lat*pi()/180)) * sin((latitude*pi()/180)) + cos(($lat*pi()/180)) * cos((latitude*pi()/180)) * cos((($lng - longitude)*pi()/180))))*180/pi())*60*1.1515) as distance FROM companies HAVING distance <= $miles ORDER BY distance ASC LIMIT xx $lat and $lng for the starting point as well as $miles for the max distance to search This works excellently. However what i want to do is twist this slightly to take a table where the users (whose home postcodes have been converted to Long Lat points ~ possibly by google) will have a MaxDistance they are prepared to travel to a venue for an event. I have changed the above statement to: SELECT id,user,longitude,latitude, (((acos(sin((51.75733*pi()/180)) * sin((latitude*pi()/180)) + cos((51.75733*pi()/180)) * cos((latitude*pi()/180)) * cos(((-0.341325 - longitude)*pi()/180))))*180/pi())*60*1.1515) as distance FROM tblusers HAVING distance <= tblusers.Maxdistance ORDER BY distance ASC LIMIT 5 the values 51.75733, and -0.341325 are the Long and Lat co-ordinates for an event location and the tblusers.Maxdistance is the column in TABLE tblusers which holds the max distance that they are willing to travel. This doesnt work. I am a noob to MySQL and therefore not sure if what i am trying to do is possible or not or if it is something that just needs the aplications of another clause. I have tried WHERE instead of HAVING but this the errors with 'distance' not being a valid column....
View Replies !
View Related
Display Results Within Results
1) I have already did a search for: "Results within results" on this site, in PHP & MySQL forums ( I think) properly...and one search resulted in over 100 pages etc. From the below structure of my DB, I would like to get the code from the below URL working on my existing data I have, but I am having trouble and I am just getting flustered.... I would eventually like to have the user "select" first a $State and then $County from a "drop down" for now, and eventually a "map" but the "drop down" for these (2) will be a must have....but I am just trying to use this function first. At present, I am unable to even get proper printed results, and I know it has to do with something on the variable call end that is screwing me up. 2) This is what I am using (learning) from: 3) This is my mySQL db structure: ....
View Replies !
View Related
Add Up Results
Not sure if I should ask this in PHP forum or this one? I think this is a simple request. I have a table with names and dollar amounts of pledges. What I need to do is grab all the amounts for the same person and then add it up to a total amount that they owe. ie: Table is like this. Don $500 Joe $100 Don $1250 Don $1250 Fred $300 I need to pull all the info that are for Don and then add up the amounts, for example this one Don owes a total of $3000.
View Replies !
View Related
Getting Results
i use mysql 4.0.22 which means i cant use subquery's right? Ive tried using them but it dident go well. This is what im trying to accomplish SELECT service FROM rate WHERE type = 'as' service must be equal to se SELECT se FROM autosurf where st = 0
View Replies !
View Related
Different Results
I have a nightmare of a problem, where in a Query I am attempting to calculate percentages. I had the query running on an earlier version of mysql and it worked fine, and it is now running on the latest version and doesn't return the correct value. This is the query : Select DISTINCT std.student_f_name, std.student_m_name, std.student_l_name, lt.time_from, lt.time_to, lt.day, TIME(rda.clockinTime), cast(($variable1/$variable2)*100 as decimal(3,2)) from students std, record_attendance rda, lecture_times lt where std.studentID = ? AND rda.studentID = std.studentID AND rda.lectureTime = lt.id AND lt.id=? This correctly returned 33.3 percent in the older version, however in the new version returns 9.9 %. Does anyone know how this can be fixed?
View Replies !
View Related
Multiple Results
I'm using PHP to display a list of statistic information about a site. Sometimes I need to retrieve mixed information from the database but I don't know which is the best method to do it. For example I need the top requested html pages and the most repeated value from a column. I dont know if I should make two different queries in the php file or it's more efficient to make only one query with an extra column like id | ip | date | page | max | --------+--------------+----------+----------+---------| 1 + 24.125.24.25 + 24/5/03 + index + 3 | 2 + 24.125.24.25 + 24/5/03 + top + 3 | 3 + 20.12.12.21 + 24/5/03 + index + 3 | 4 + 200.12.24.25 + 24/5/03 + left + 3 | 5 + 24.1.6.255 + 24/5/03 + left + 3 | 6 + 24.125.24.12 + 24/5/03 + index + 3 |
View Replies !
View Related
Displaying SQL Results
I have made a database with all different things (venue, team names etc), the team names display fine, but I am using different code for venues (counting) and these just won't display at all, I am really new to SQL so don't know what to do to fix it! The code for getting the SQL is $sql = 'select venue,count(venue) as frequency from matches group by venue limit 300'; which works fine in phpMyAdmin and displays what I want to, but I don't know how to display it on my page! I do have the sql connection info in the php page too, plus the code which is used for displaying all my other pages but doesn't want to work now! I would like a table with headers of Venue & Number if possible, then the venues and number next to them, as it displays in phpMyAdmin
View Replies !
View Related
Matching Results
Suppose you have two tables: A and B. Both tables have the same columns: col1, col2, col3, etc. So my records are distributed in two different tables that have the same format. I know, this is not good normalization design, but that is the way they are setup right now. The question is: how do I query both in a single sql statement, combine the results of the query get all the records from both, and list the result in alphabetical order? I can do: select * from A ORDER BY status; and I can also run: select * from B ORDER BY status; But how do I run both at the same time and get a single output from them?
View Replies !
View Related
Count Results
does there exist a less resource intensive way on how to count the results of a query? I mean I know that way: Doing a query on mysql and then using mysql_num_rows for fetching the amount of results, but I guess there should be an easyer way, that probably uses a mySQL syntax for that? Probably COUNT()? I read through the explanation of it and believe this could be the way how to do, but I can´t figure out how exactly the syntax would look alike
View Replies !
View Related
Mysql Results
What i'm trying to do is count is say I ran this sql query - SELECT DISTINCTROW length FROM list - it would show only one of each result (if there were rows with duplicate lengths). Is there a query which I could do that would also count how many duplicates there were if any
View Replies !
View Related
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 !
View Related
Results In Error
I'm trying to create a MySQL database using PHP with the following command: $create = mysql_query("CREATE DATABASE IF NOT EXISTS moviesite") or die(mysql_error()); This results in the following error: Access denied for user 'bp5am'@'%' to database 'moviesite' * I am able to connect to the database using: mysql_connect * I am able to create that very database manually using dos * Apache loads files as it should * PHP is parsed correctly
View Replies !
View Related
Getting NULL For Results
SELECT DATEDIFF('date_hired,'date_fired') from users and i get all NULL for results i think i should be getting number of days apart from the two dates date_hired and date_fired are both date columns so whats wrong with this
View Replies !
View Related
Getting Results From 3 Tables
I have three tables: Person with key nId plus other fields, volchar with fields nVolCharId, nId, nActivityNum and activitynames with two fields nActivityNum and txtActivityName. One person can be signed up for many activity so I want my output to look like: LastName, FirstName, etc plus a list of txtActivityNames (eg. Smith, Bob (activities: golf, swimming, basketball)). Is there a way to structure a query to get those kind of results so they can be displayed in table form?
View Replies !
View Related
Getting Search Results
I have a table of posts which contains parent post and replies (structure below). I am having difficulty returning search results from this. What I want to do it search all rows title and body fields for matches, however I only want to return a rows parent in the results regardless of weather the parent contains the search term or not. What I can't figure out how to do is remove child rows from the search results while also getting the parent row if it does not contain the search term.Structure: id parent_id -> is 0 if post is the parent user_id title body I am also joining the results to the users table so that I can display the username associated with the post in the results.
View Replies !
View Related
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 !
View Related
|
TRACKER
